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