Loop / Cursor help

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  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
--

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...

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.
>

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

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
--

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
> --
>

David,

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

Madhivanan

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

Thanks Erland

Madhivanan