Creating data for dates not in the data set with SQL Server SQL to create a Periods Table

One of the more common questions here is how do I fabricate dates for periods not covered in my data?

An example would be that you want to graph the entire month, but only have data for 20 days, yet Mgmt insists that the graph show all of the dates.

My standard Reporting Database/Data Warehosuing solution is to create a Periods table which covers all dates (or period sensitivity required by your reporting system), and then LO your data to it. Now you'll have all dates in the results.

Although I use different columns depending upon the Business Intelligence requirements, the following is the SQL Code to create a generic one:

CREATE TABLE [dbo].[DWPeriod] (
[PERIODID] [int] IDENTITY (1, 1) NOT NULL ,
[dtsActivityDate] [smalldatetime] NULL ,
[intYear] [int] NULL ,
[intQuarter] [int] NULL ,
[intMonthNum] [int] NULL ,
[chrMonthName] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chrMonthAbbrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intWeekNum] [int] NULL ,
[intDayNum] [int] NULL ,
[chrDayName] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chrDayAbbrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intFiscalYear] [int] NULL ,
[intFiscalQuarter] [int] NULL ,
[intFiscalMonthNum] [int] NULL ,
[intFiscalWeekNum] [int] NULL ,
[bitAcctCycleEnd] [bit] NULL ,
[bitPublicHoliday] [bit] NULL ,
[bitNonWorkingDay] [bit] NULL ,
[bitSpecialWorkDay1] [bit] NULL ,
[bitSpecialWorkDay2] [bit] NULL ,
[dtmCreated] [datetime] NULL ,
[chvCreator] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

And now for an SP to populate it:

CREATE PROCEDURE [dbo].[ap_DW_PopulateDWPeriod] AS
-- Declare the variables
declare @dtsActivityDate as smalldatetime
declare @intYear as int
declare @intQuarter as int
declare @intMonthNum as int
declare @chrMonthName as char(20)
declare @chrMonthAbbrev as char(3)
declare @intWeekNum as int
declare @intDayNum as int
declare @chrDayName as char(15)
declare @chrDayAbbrev as char(3)
declare @intFiscalYear as int
declare @intFiscalQuarter as int
declare @intFiscalMonthNum as int
declare @intFiscalWeekNum as int
declare @bitAcctCycleEnd as bit
declare @bitPublicHoliday as bit
declare @bitNonWorkingDay as bit
declare @bitSpecialWorkDay1 as bit
declare @bitSpecialWorkDay2 as bit
declare @dtmCreated as datetime
declare @chvCreator as varchar(15)

--Set the non-changing variables
set @dtmCreated = getdate()
set @chvCreator = 'Kai Molvig'
set @dtsActivityDate = '1/1/1990'
--Begin the loop
while @dtsActivityDate < '1/1/2015'
BEGIN
set @intYear = year(@dtsActivityDate )
set @intQuarter = datepart(quarter,@dtsActivityDate)
set @intMonthNum = month(@dtsActivityDate)
set @chrMonthName = datename(month,@dtsActivityDate)
set @chrMonthAbbrev = DATENAME(m,@dtsActivityDate)
set @intWeekNum = datepart(wk , @dtsActivityDate)
set @intDayNum = day(@dtsActivityDate)
set @chrDayName = datename(dw,@dtsActivityDate)
set @chrDayAbbrev = substring(@chrDayName,1,3)
-- Determine the Fiscal Year
If month(@dtsActivityDate) < 7
set @intFiscalYear = @intYear
else
set @intFiscalYear = @intYear+1
-- Determine the Fiscal Quarter
if @intQuarter = 1
set @intFiscalQuarter = 3
if @intQuarter = 2
set @intFiscalQuarter = 4
if @intQuarter = 3
set @intFiscalQuarter = 1
if @intQuarter = 4
set @intFiscalQuarter = 2
-- Determine the Fiscal Month
if @intMonthNum > 6
set @intFiscalMonthNum = @intMonthNum-6
else
set @intFiscalMonthNum = @intMonthNum+6
-- Currently not populated, perhaps drop it as it isn't used???
set @intFiscalWeekNum = 0
set @bitAcctCycleEnd = 0
-- Need to learn the holidays
set @bitPublicHoliday = 0
-- Determine whether it's a weekend - other dates might be non-working days, TBD
if datepart(dw,@dtsActivityDate) in (1,7)
set @bitNonWorkingDay = 1
else
set @bitNonWorkingDay = 0
-- Future need special work days
set @bitSpecialWorkDay1 = 0
set @bitSpecialWorkDay2 = 0
-- Do the insert
insert into dwperiod (dtsActivityDate, intYear, intQuarter, intMonthNum, chrMonthName, chrMonthAbbrev, intWeekNum, intDayNum, chrDayName, chrDayAbbrev, intFiscalYear, intFiscalQuarter, intFiscalMonthNum, intFiscalWeekNum, bitAcctCycleEnd, bitPublicHoliday, bitNonWorkingDay, bitSpecialWorkDay1, bitSpecialWorkDay2, dtmCreated, chvCreator)
values (@dtsActivityDate, @intYear, @intQuarter, @intMonthNum, @chrMonthName, @chrMonthAbbrev, @intWeekNum, @intDayNum, @chrDayName, @chrDayAbbrev, @intFiscalYear, @intFiscalQuarter, @intFiscalMonthNum, @intFiscalWeekNum, @bitAcctCycleEnd, @bitPublicHoliday, @bitNonWorkingDay, @bitSpecialWorkDay1, @bitSpecialWorkDay2, @dtmCreated, @chvCreator )
-- Increment the @dtsActivityDate
set @dtsActivityDate = dateadd(d,1,@dtsActivityDate)
END

-k