INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formula Help

Creating data for dates not in the data set with SQL Server SQL to create a Periods Table by synapsevampire
Posted: 2 Dec 03 (Edited 2 Nov 04)

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

Back to Business Objects: Crystal Reports 1 Formulas FAQ Index
Back to Business Objects: Crystal Reports 1 Formulas Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close