How can I generate a temporary table filled with dates in SQL Server 2000?
This of course will not work in SQL-Server 2000 but in a modern database where you don't want to create a permanent table. You can use a table variable instead creating a table so you can left join the data try this. Change the DAY to HOUR etc to change the increment type.
declare @CalendarMonths table (date DATETIME, PRIMARY KEY (date)
)
DECLARE
@basedate DATETIME,
@offset INT
SELECT
@basedate = '01 Jan 2014',
@offset = 1
INSERT INTO @CalendarMonths SELECT @basedate
WHILE ( DATEADD(DAY, @offset, @basedate) < CURRENT_TIMESTAMP)
BEGIN
INSERT INTO @CalendarMonths SELECT DATEADD(HOUR, @offset, date) FROM @CalendarMonths where DATEADD(DAY, @offset, date) < CURRENT_TIMESTAMP
SELECT @offset = @offset + @offset
END
This will quickly populate a table with 170 years worth of dates.
CREATE TABLE CalendarMonths (
date DATETIME,
PRIMARY KEY (date)
)
DECLARE
@basedate DATETIME,
@offset INT
SELECT
@basedate = '01 Jan 2000',
@offset = 1
WHILE (@offset < 2048)
BEGIN
INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
SELECT @offset = @offset + @offset
END
You can then use it by LEFT joining on to that table, for the range of dates you require.
I needed something similar, but all DAYS instead of all MONTHS.
Using the code from MatBailie as a starting point, here's the SQL for creating a permanent table with all dates from 2000-01-01 to 2099-12-31:
CREATE TABLE _Dates (
d DATE,
PRIMARY KEY (d)
)
DECLARE @dIncr DATE = '2000-01-01'
DECLARE @dEnd DATE = '2100-01-01'
WHILE ( @dIncr < @dEnd )
BEGIN
INSERT INTO _Dates (d) VALUES( @dIncr )
SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END
I would probably use a Calendar table. Create a permanent table in your database and fill it with all of the dates. Even if you covered a 100 year range, the table would still only have ~36,525 rows in it.
CREATE TABLE dbo.Calendar (
calendar_date DATETIME NOT NULL,
is_weekend BIT NOT NULL,
is_holiday BIT NOT NULL,
CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED (calendar_date)
)
Once the table is created, just populate it once in a loop, so that it's always out there and available to you.
Your query then could be something like this:
SELECT
C.calendar_date,
0 AS trials,
0 AS sales
FROM
dbo.Calendar C
WHERE
C.calendar_date BETWEEN @start_date AND @end_date AND
DAY(C.calendar_date) = 1
You can join in the Customers table however you need to, outer joining on FirstOfMonth(InsertDate) = C.calendar_date
if that's what you want.
You can also include a column for day_of_month if you want which would avoid the overhead of calling the DAY()
function, but that's fairly trivial, so it probably doesn't matter one way or another.