|
Home > Archive > Microsoft SQL Server forum > February 2006 > Loop / Cursor help
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Loop / Cursor help
|
|
| woodfoot 2006-02-20, 9:23 am |
| Having a brain cramp here and don't know where to start. I have 2 tables:
vehicles and vehicle_useage. What I would like to do is this:
For each distinct vehicle in the vehicle table, I want to make entries
for each day of the month taken from a given date. This routine will be
scheduled to fire off once a month and populate the vehicle_useage table
with vehicle use_dates for each day of the current month and for each VIN
from the vehicle table.
vehicle table:
VIN emp_id
------------ ------
VIN123456789 620123
VIN987654321 620123
vehicle_useage table:
use_date VIN miles
----------- ------------ -----
02/01/2006 VIN123456789 0
02/02/2006 VIN123456789 0
02/03/2006 VIN123456789 0
02/04/2006 VIN123456789 0
etc....
02/01/2006 VIN987654321 0
02/02/2006 VIN987654321 0
02/03/2006 VIN987654321 0
02/04/2006 VIN987654321 0
etc...
Much appreciated for any help you can give...
| |
| David Portas 2006-02-20, 9:23 am |
| woodfoot wrote:
> Having a brain cramp here and don't know where to start. I have 2 tables:
> vehicles and vehicle_useage. What I would like to do is this:
>
> For each distinct vehicle in the vehicle table, I want to make entries
> for each day of the month taken from a given date. This routine will be
> scheduled to fire off once a month and populate the vehicle_useage table
> with vehicle use_dates for each day of the current month and for each VIN
> from the vehicle table.
>
> vehicle table:
>
> VIN emp_id
> ------------ ------
> VIN123456789 620123
> VIN987654321 620123
>
> vehicle_useage table:
>
> use_date VIN miles
> ----------- ------------ -----
> 02/01/2006 VIN123456789 0
> 02/02/2006 VIN123456789 0
> 02/03/2006 VIN123456789 0
> 02/04/2006 VIN123456789 0
> etc....
> 02/01/2006 VIN987654321 0
> 02/02/2006 VIN987654321 0
> 02/03/2006 VIN987654321 0
> 02/04/2006 VIN987654321 0
> etc...
>
> Much appreciated for any help you can give...
Use a Calendar table (one row per date):
INSERT INTO vehicle_useage (use_date, vin, miles)
SELECT C.cal_date, V.vin, 0
FROM vehicles AS V
JOIN calendar AS C
ON C.cal_date BETWEEN '20060201' AND 20060228' ;
Alternatively perhaps it would be better to insert the useage rows only
when you want to update the mileage figure. You can still report on
each day by joining to the calendar.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
| |
| Tom Moreau 2006-02-20, 9:23 am |
| Please don't multi-post. I answered this in
microsoft.public.sqlserver.programming.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"woodfoot" <jdperry784@yahoo.com> wrote in message
news:Xns977055E12663
Ejamesperrycomcastne
t@216.196.97.136...
Having a brain cramp here and don't know where to start. I have 2 tables:
vehicles and vehicle_useage. What I would like to do is this:
For each distinct vehicle in the vehicle table, I want to make entries
for each day of the month taken from a given date. This routine will be
scheduled to fire off once a month and populate the vehicle_useage table
with vehicle use_dates for each day of the current month and for each VIN
from the vehicle table.
vehicle table:
VIN emp_id
------------ ------
VIN123456789 620123
VIN987654321 620123
vehicle_useage table:
use_date VIN miles
----------- ------------ -----
02/01/2006 VIN123456789 0
02/02/2006 VIN123456789 0
02/03/2006 VIN123456789 0
02/04/2006 VIN123456789 0
etc....
02/01/2006 VIN987654321 0
02/02/2006 VIN987654321 0
02/03/2006 VIN987654321 0
02/04/2006 VIN987654321 0
etc...
Much appreciated for any help you can give...
| |
| woodfoot 2006-02-20, 9:23 am |
| Sorry, I have not posted to USENET groups before.
"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in news:sKjKf.3300$%14.163054
@news20.bellglobal.com:
> Please don't multi-post. I answered this in
> microsoft.public.sqlserver.programming.
>
| |
| woodfoot 2006-02-20, 9:23 am |
| Is there a way to do this without the use of a calendar table?
thx
| |
| David Portas 2006-02-20, 9:23 am |
| woodfoot wrote:
> Is there a way to do this without the use of a calendar table?
> thx
You could create the calendar in a table variable. It seems a bit
pointless to do that each month though. Calendar tables are useful for
all sorts of queries and reports. It's probably worth keeping one in
your database whether you use it for this or not.
DECLARE @t TABLE (cal_date DATETIME PRIMARY KEY);
DECLARE @month DATETIME;
SET @month = '20060201';
INSERT INTO @t VALUES (@month);
WHILE (SELECT MAX(cal_date) FROM @t)< DATEADD(MONTH,1,@mon
th)
INSERT INTO @t (cal_date)
SELECT DATEADD(DAY,
DATEDIFF(DAY,@month,
cal_date)+1,
(SELECT MAX(cal_date) FROM @t))
FROM @t;
INSERT INTO vehicle_useage (use_date, vin, miles)
SELECT C.cal_date, V.vin, 0
FROM vehicles AS V
JOIN @t AS C
ON C.cal_date >= @month
AND C.cal_date < DATEADD(MONTH,1,@mon
th);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
| |
| woodfoot 2006-02-20, 11:23 am |
| Beautiful. Worked great, thanks a bunch David.
"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in
news:1140447821.251324.128220@z14g2000cwz.googlegroups.com:
> woodfoot wrote:
>
> You could create the calendar in a table variable. It seems a bit
> pointless to do that each month though. Calendar tables are useful for
> all sorts of queries and reports. It's probably worth keeping one in
> your database whether you use it for this or not.
>
> DECLARE @t TABLE (cal_date DATETIME PRIMARY KEY);
> DECLARE @month DATETIME;
>
> SET @month = '20060201';
>
> INSERT INTO @t VALUES (@month);
>
> WHILE (SELECT MAX(cal_date) FROM @t)< DATEADD(MONTH,1,@mon
th)
> INSERT INTO @t (cal_date)
> SELECT DATEADD(DAY,
> DATEDIFF(DAY,@month,
cal_date)+1,
> (SELECT MAX(cal_date) FROM @t))
> FROM @t;
>
> INSERT INTO vehicle_useage (use_date, vin, miles)
> SELECT C.cal_date, V.vin, 0
> FROM vehicles AS V
> JOIN @t AS C
> ON C.cal_date >= @month
> AND C.cal_date < DATEADD(MONTH,1,@mon
th);
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
| |
| Madhivanan 2006-02-25, 9:44 am |
| David,
Why does select * from @t returns 32 rows?
I expected it to return 28 rows only
Madhivanan
| |
| Erland Sommarskog 2006-02-25, 9:45 am |
| Madhivanan (madhivanan2001@gmai
l.com) writes:
> Why does select * from @t returns 32 rows?
> I expected it to return 28 rows only
Watch this part of the output:
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
(4 row(s) affected)
(8 row(s) affected)
(16 row(s) affected)
See what is going on? David is doubling the number of rows he inserts
each time. That way the loops can run fewer iterations and be faster.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| Madhivanan 2006-02-25, 9:45 am |
| Thanks Erland
Madhivanan
|
|
|
|
|