20 January 2008 14:09 tonyrogerson

Calendar table example and how to use it

Want a calendar table? Here’s the one I tend to use and an example at the bottom of how to summarise using it.

It can be expanded if you are interested in working / non working days; it’s basically just dropping down at day level and have a column to indicate a working / non working day.

Another method I use when summarising to month level is SUM( CASE <exp> ) but that’s another article that no doubt will show up on here in the coming weeks J.

There is one glaring problem with this and it’s because of SQL Server’s datetime precision, i.e. it’s only precise to 300th millisecond so the end of my day is 23:59:59.997.

{Modified on 21st Jan 2008 to include ISO week and SQL Server week numbers (starting 1 jan).

create table calendar (

    calendar_id             int not null identity constraint pk_calendar primary key clustered,

    period_start            datetime    not null,

    period_end              as cast( left( convert( varchar(60),

                 dateadd( day, -1, case period_type

                                        when 'WY' then dateadd( week, 1, period_start )

                                        when 'WI' then dateadd( week, 1, period_start )

                                        when 'M' then dateadd( month, 1, period_start )

                                        when 'Q' then dateadd( month, 3, period_start )

                                        when 'H' then dateadd( month, 6, period_start )

                                        when 'Y' then dateadd( year, 1, period_start )

                                   end ), 121 ), 10 ) + 'T23:59:59.997' as datetime ),  

    period_name             varchar(50) not null,

    period_type             varchar(2)  not null    check( period_type in ( 'WY', 'WI', 'M', 'Q', 'H', 'Y' ) ),

        constraint uk_calendar_period_start_type unique( period_start, period_type ),

        constraint uk_calendar_period_name unique( period_name, period_type )

)

go

/***

    period_type

        WY - Week in Year according to SQL Server; WI - Week in Year according to ISO

        M  - Month

        Q  - Quarter

        H  - Half

        Y  - Year

 

***/

 

 

--  Populate the calendar (month based stuff first; that is periods are aligned with start of month)

declare @start_date datetime

declare @date datetime

set @start_date = '20060101'

set @date = @start_date

 

set nocount on

 

while @date <= dateadd( year, 1, current_timestamp )

begin

    insert calendar( period_start, period_name, period_type )

        values( @date, left( datename( month, @date ), 3 ) + '-' + datename( year, @date ), 'M' )

 

    if datepart( month, @date ) in ( 1, 4, 7, 10 )

        insert calendar( period_start, period_name, period_type )

            values( @date, 'Q' + case datepart( month, @date )

                                    when 1 then '1'

                                    when 4 then '2'

                                    when 7 then '3'

                                    when 10 then '4'

                                 end + '-' + datename( year, @date ), 'Q' )

 

    if datepart( month, @date ) in ( 1, 7 )

        insert calendar( period_start, period_name, period_type )

            values( @date, 'H' + case datepart( month, @date )

                                    when 1 then '1'

                                    when 7 then '2'

                                 end + '-' + datename( year, @date ), 'H' )

 

    if datepart( month, @date ) = 1

        insert calendar( period_start, period_name, period_type )

            values( @date, 'Y' + datename( year, @date ), 'Y' )

           

    set @date = dateadd( month, 1, @date )

 

end

go

 

 

--  First we need something to give us our ISO week number

create function dbo.fn_iso_week_number(

    @date datetime )

returns int

as

begin

/***

 

    The code here is from the article http://www.sqlservercentral.com/articles/Advanced+Querying/onthetrailoftheisoweek/1675/ by Christoffer Hedgate 20 Jan 2005.

    Note, change 01-04 in Christoffer's article because dates need to be passed as YYYYMMDD in SQL Server to avoid any inconsistency.

 

****/

 

    return( CASE

 

     -- Exception where @date is part of week 52 (or 53) of the previous year

     WHEN @date < CASE (DATEPART(dw, CAST(YEAR(@date) AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

      WHEN 1 THEN CAST(YEAR(@date) AS CHAR(4)) + '0104'

      WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      END

     THEN

      (DATEDIFF(d,

       CASE (DATEPART(dw, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

       WHEN 1 THEN CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104'

       WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       ELSE DATEADD(d, -6, CAST(YEAR(@date) - 1 AS CHAR(4)) + '0104')

       END,

       @date

      ) / 7) + 1

 

     -- Exception where @date is part of week 1 of the following year

     WHEN @date >= CASE (DATEPART(dw, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

      WHEN 1 THEN CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104'

      WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      ELSE DATEADD(d, -6, CAST(YEAR(@date) + 1 AS CHAR(4)) + '0104')

      END

     THEN 1

 

     ELSE

      -- Calculate the ISO week number for all dates that are not part of the exceptions above

      (DATEDIFF(d,

      CASE (DATEPART(dw, CAST(YEAR(@date) AS CHAR(4)) + '0104') + @@DATEFIRST - 1) % 7

      WHEN 1 THEN CAST(YEAR(@date) AS CHAR(4)) + '0104'

      WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '0104')

      END,

      @date

      ) / 7) + 1

     END

    )

end
go

 

--  Insert Week in Year and ISO Week rows

declare @start_date datetime

declare @date datetime

set @start_date = '20060101'

set @date = @start_date

 

set nocount on

 

while @date <= dateadd( year, 1, current_timestamp )

begin

    insert calendar( period_start, period_name, period_type )

        values( @date, datename( year, dateadd( year, case when datepart( month, @date ) = 1 and dbo.fn_iso_week_number( @date ) > 50 then -1 else 0 end, @date ) ) + '-W' + right( '0' + cast( dbo.fn_iso_week_number( @date ) as varchar(2) ), 2 ), 'WI' )

 

    insert calendar( period_start, period_name, period_type )

        values( @date, datename( year, @date ) + '-W' + right( '0' + cast( datepart( week, @date ) as varchar(2) ), 2 ), 'WY' )

 

    set @date = dateadd( week, 1, @date )

 

end

go

 

select *

from calendar

order by period_type, period_start

go

 

--  Show months with no orders...

--  Example use

declare @orders table (

    order_date  datetime    not null,

    order_count int         not null

)

 

insert @orders ( order_date, order_count ) values( '20080105', 1 )

insert @orders ( order_date, order_count ) values( '20080106', 1 )

insert @orders ( order_date, order_count ) values( '20080107', 1 )

 

insert @orders ( order_date, order_count ) values( '20080405', 1 )

insert @orders ( order_date, order_count ) values( '20080406', 1 )

 

--  Sum by month...

select c.period_start,

       total_orders = sum( o.order_count )

from calendar c

    inner join @orders o on o.order_date between c.period_start and c.period_end

where period_type = 'M'

  and period_start between '20080101' and '20080601'

group by c.period_start

 

--  Sum by month and show months that have no orders...

select c.period_start,

       total_orders = sum( o.order_count )

from calendar c

    left outer join @orders o on o.order_date between c.period_start and c.period_end

where period_type = 'M'

  and period_start between '20080101' and '20080601'

group by c.period_start

 

Filed under:

Comments

# re: Calendar table example and how to use it

21 January 2008 07:23 by Uri Dimant

Hi Tony

If I run --Populate Populate the calendar block I'm getting the error

Msg 2627, Level 14, State 1, Line 19

Violation of UNIQUE KEY constraint 'uk_calendar_period_start_type'. Cannot insert duplicate key in object 'dbo.calendar'.

The statement has been terminated.

I managed to work and it looks great. Thank you

# re: Calendar table example and how to use it

21 January 2008 07:58 by tonyrogerson

Hi Uri, Think you must of forget the TRUNCATE TABLE after the test data.

I just realised I duplicated the examples at the bottom so I've fixed that and moved the TRUNCATE TABLE down to make it more visible.

I'll make a day and week version shortly.

Tony.

# Summarizing by Week | keyongtech

18 January 2009 16:51 by Summarizing by Week | keyongtech

Pingback from  Summarizing by Week | keyongtech

# Getting a proper Count for a month - Microsoft SQL Server answers

Pingback from  Getting a proper Count for a month - Microsoft SQL Server  answers