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