Posted by woodfoot on 10/01/83 11:40

Beautiful. Worked great, thanks a bunch David.


"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in
news:1140447821.251324.128220@z14g2000cwz.googlegroups.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/ms130214(en-US,SQL.90).aspx
> --
>

[Back to original message]