Loop / Cursor help

[woodfoot]

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]

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/m...S,SQL.90).aspx
--

[Tom Moreau]

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" <jd********@yahoo.com> wrote in message
news:Xn**********************************@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]

Sorry, I have not posted to USENET groups before.

"Tom Moreau" <to*@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]

Is there a way to do this without the use of a calendar table?
thx

[David Portas]

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,@month)
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,@month);

--
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/m...S,SQL.90).aspx
--

[woodfoot]

Beautiful. Worked great, thanks a bunch David.
"David Portas" <RE****************************@acm.org> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

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,@month)
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,@month);

--
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/m...S,SQL.90).aspx
--

[Madhivanan]

David,

Why does select * from @t returns 32 rows?
I expected it to return 28 rows only

Madhivanan

[Erland Sommarskog]

Madhivanan (ma************@gmail.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Madhivanan]

Thanks Erland

Madhivanan