Posted by David Portas on 10/01/42 11:40

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

[Back to original message]