|
|
Posted by David Portas on 10/01/21 11:40
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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|