September 7, 2016 at 9:24 pm
Comments posted to this topic are about the item The Bones of SQL - The Calendar Table
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 2:53 am
Great article
Could you change the where-clauses to use proper date-variables instead of strings
WHERE CalendarDate BETWEEN convert(date,'2/27/2016',101) and convert(date,'3/1/2016',101)
instead of
WHERE CalendarDate BETWEEN '2/27/2016' and '3/1/2016'
September 8, 2016 at 3:21 am
Jo Pattyn (9/8/2016)
Great articleCould you change the where-clauses to use proper date-variables instead of strings
WHERE CalendarDate BETWEEN convert(date,'2/27/2016',101) and convert(date,'3/1/2016',101)
instead of
WHERE CalendarDate BETWEEN '2/27/2016' and '3/1/2016'
Is there anything to be aware of using e.g. '27 February 2016' instead of conversions? Locale/language issues I guess?
September 8, 2016 at 5:05 am
I have a table that numbers working days and assigns the number of the next working day to any non-working day, so durations can still be calculated for events involving non-working days (in this case any days when the office is closed, not just bank holidays).
15 Dec 20152861
16 Dec 20152862
17 Dec 20152863
18 Dec 20152864
19 Dec 20152865
20 Dec 20152865
21 Dec 20152865
22 Dec 20152866
23 Dec 20152867
24 Dec 20152868
25 Dec 20152868
26 Dec 20152868
27 Dec 20152868
28 Dec 20152868
29 Dec 20152868
30 Dec 20152869
31 Dec 20152870
01 Jan 20162871
02 Jan 20162871
03 Jan 20162871
04 Jan 20162871
05 Jan 20162872
September 8, 2016 at 5:41 am
Nice approach. I've achieved similar adapting code by Dusty. I've added in a truckload of columns, including many offsets which are particularly useful for automatically setting prompts in reporting. It also handles manual / rule based control of the financial month which tends to lag the calendar month by a few weeks.
USE [DWH_Config]
GO
/****** Object: Table [control].[g_dwh_control] Script Date: 8/09/2016 9:38:40 PM ******/
DROP TABLE [control].[g_dwh_control]
GO
/****** Object: Table [control].[g_dwh_control] Script Date: 8/09/2016 9:38:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [control].[g_dwh_control](
[category_id] [int] NOT NULL,
[category_name] [nvarchar](50) NOT NULL,
[key_id] [int] NOT NULL,
[key_name] [nvarchar](50) NOT NULL,
[key_value] [int] NULL,
[key_valuetext] [nvarchar](50) NULL
) ON [PRIMARY]
GO
insert into dwh_config.control.g_dwh_control (category_id, category_name, key_name, key_value) values (1, 'Dates', 1, 'FinMonth', 201605)
insert into dwh_config.control.g_dwh_control (category_id, category_name, key_name, key_value) values (1, 'Dates', 2, 'OpDate', 20160502)
use DWH
IF EXISTS(SELECT * FROM sys.tables WHERE object_id = object_id('DWH.common.D_Date') AND NAME ='d_date')
DROP TABLE DWH.common.D_Date;
CREATE TABLE DWH.common.D_Date (
-- Generic Date
[date_sk] [int] NOT NULL
, [date_sql] [datetime] NOT NULL
, [date_name] [varchar](10) NULL
-- Day
, [day_no] [tinyint] NOT NULL
, [day_suffix] [varchar](4) NOT NULL
, [day_of_week_name] [varchar](9) NOT NULL
, [day_of_week_shortname] [varchar](9) NOT NULL
, [day_of_week_no] [int] NOT NULL
, [day_of_week_in_month] [tinyint] NOT NULL
, [day_of_year_no] [int] NOT NULL
-- Week
, [week_sk] [int] NOT NULL
, [week_of_year_no] [tinyint] NOT NULL
, [week_of_month_no] [tinyint] NOT NULL
, [first_day_of_week] [datetime] NULL
, [last_day_of_week] [datetime] NULL
, [week_day_flag] [bit] NOT NULL
-- Month
, [month_sk] [int] NOT NULL
, [month_no] [tinyint] NOT NULL
, [month_name] [varchar](9) NOT NULL
, [month_shortname] [varchar](9) NOT NULL
, [first_day_of_month] [datetime] NOT NULL
, [last_day_of_month] [datetime] NOT NULL
-- Quarter
, [quarter_no] [tinyint] NOT NULL -- 2
, [quarter_name] [varchar](6) NOT NULL -- Second
, [quarter_shortname] [varchar](6) NOT NULL -- Q2
, [first_day_of_quarter] [datetime] NULL
, [last_day_of_quarter] [datetime] NULL
-- Year
, [year_no] [int] NOT NULL
, [first_day_of_year] [datetime] NOT NULL
, [last_day_of_year] [datetime] NOT NULL
-- Cal Dates
, [cal_day_offset] [int] NOT NULL
, [cal_week_offset] [int] NOT NULL
, [cal_month_offset] [int] NOT NULL
, [cal_quarter_offset] [int] NOT NULL
, [cal_year_offset] [int] NOT NULL
-- Op Month / Quarter / Year
, [op_day_offset] [int] NOT NULL
, [op_week_offset] [int] NOT NULL
, [op_month_offset] [int] NOT NULL
, [op_quarter_offset] [int] NOT NULL
, [op_year_offset] [int] NOT NULL
-- Fin Offsets
, [fin_month_offset] [int] NOT NULL
, [fin_quarter_offset] [int] NOT NULL
, [fin_year_offset] [int] NOT NULL
, [fin_year_name] [varchar](6) NOT NULL -- FY2016
, [etl_create_date] datetime
CONSTRAINT [PK_D_Date] PRIMARY KEY CLUSTERED
(
[date_sk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
-- -=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=-=-=-
-- common.D_Date
-- 2/5/16, Andrew Mosey
-- Based on Initial code from SQL Dusty https://sqldusty.com/2012/04/12/create-date-dimension-script/
-- Adapted for Reporting
--DBCC CHECKIDENT (common.D_Date, RESEED, 60000) --In case you need to add earlier dates later.
DECLARE @tmpDOW TABLE (DOW INT, Cntr INT)--Table for counting DOW occurance in a month
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(1,0)--Used in the loop below
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(2,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(3,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(4,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(5,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(6,0)
INSERT INTO @tmpDOW(DOW, Cntr) VALUES(7,0)
DECLARE @StartDate datetime
, @EndDate datetime
, @FinMonth datetime
, @OpDate datetime
, @Date datetime
, @WDofMonth INT
, @CurrentMonth INT
, @CurrentDate date = getdate()
SELECT @StartDate = '1990-01-01' -- -- Set The start and end date
, @EndDate = '2031-01-01'--Non inclusive. Stops on the day before this.
, @CurrentMonth = 1 --Counter used in loop below.
SELECT @Date = @StartDate
SELECT @FinMonth =
datefromparts(
substring(cast(key_value as nvarchar(8)), 1, 4),
substring(cast(key_value as nvarchar(8)), 5, 2),
1)
from dwh_config.control.g_dwh_control where category_id = 1 and key_id = 1
SELECT @OpDate =
datefromparts(
substring(cast(key_value as nvarchar(8)), 1, 4),
substring(cast(key_value as nvarchar(8)), 5, 2),
substring(cast(key_value as nvarchar(8)), 7, 2))
from dwh_config.control.g_dwh_control where category_id = 1 and key_id = 2
WHILE @Date < @EndDate
BEGIN
IF DATEPART(MONTH,@Date) <> @CurrentMonth
BEGIN
SELECT @CurrentMonth = DATEPART(MONTH,@Date)
UPDATE @tmpDOW SET Cntr = 0
END
UPDATE @tmpDOW
SET Cntr = Cntr + 1
WHERE DOW = DATEPART(DW,@DATE)
SELECT @WDofMonth = Cntr
FROM @tmpDOW
WHERE DOW = DATEPART(DW,@DATE)
INSERT INTO common.D_Date
(
[date_sk]
,[date_sql]
,[date_name]
,[day_no]
,[day_suffix]
,[day_of_week_name]
,[day_of_week_shortname]
,[day_of_week_no]
,[day_of_week_in_month]
,[day_of_year_no]
,[week_sk]
,[week_of_year_no]
,[week_of_month_no]
,[first_day_of_week]
,[last_day_of_week]
,[week_day_flag]
,[month_sk]
,[month_no]
,[month_name]
,[month_shortname]
,[first_day_of_month]
,[last_day_of_month]
,[quarter_no]
,[quarter_name]
,[quarter_shortname]
,[first_day_of_quarter]
,[last_day_of_quarter]
,[year_no]
,[first_day_of_year]
,[last_day_of_year]
,[cal_day_offset]
,[cal_week_offset]
,[cal_month_offset]
,[cal_quarter_offset]
,[cal_year_offset]
,[op_day_offset]
,[op_week_offset]
,[op_month_offset]
,[op_quarter_offset]
,[op_year_offset]
,[fin_month_offset]
,[fin_quarter_offset]
,[fin_year_offset]
,[fin_year_name]
,[etl_create_date]
)
SELECT
CONVERT(VARCHAR,@Date,112) [date_sk], --TO MAKE THE DateSK THE YYYYMMDD FORMAT UNCOMMENT THIS LINE COMMENT FOR AUTOINCREMENT
@Date [date_sql]
, RIGHT('0' + convert(varchar(2),MONTH(@Date)),2) + '/' + Right('0' + convert(varchar(2),DAY(@Date)),2) + '/' + convert(varchar(4),YEAR(@Date)) [date_name]
, DATEPART(DAY,@DATE) [day_no]
, CASE
WHEN DATEPART(DAY,@DATE) IN (11,12,13) THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 1 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 2 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DAY,@DATE),1) = 3 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'
END AS [day_suffix]
, CASE DATEPART(DW, @DATE)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END AS [day_of_week_name]
, CASE DATEPART(DW, @DATE)
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
END AS [day_of_week_shortname]
,DATEPART(DW, @DATE) AS [day_of_week_no]
, @WDofMonth [day_of_week_in_month] --Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.
, DATEPART(dy,@Date) [day_of_year_no] --Day of the year. 0 -- 365/366
, substring(CONVERT(VARCHAR,@Date,112), 1, 4) + right('0' + cast(DATEPART(ww,@Date) as nvarchar(2)), 2) [week_sk]
, DATEPART(ww,@Date) [week_of_year_no]--0-52/53
, DATEPART(ww,@Date) + 1 -
DATEPART(ww,CAST(DATEPART(mm,@Date) AS VARCHAR) + '/1/' + CAST(DATEPART(yy,@Date) AS VARCHAR)) [week_of_month_no]
, dateadd(day, -DATEPART(DW, @DATE)+1, @DATE) [first_day_of_week]
, dateadd(millisecond, -3, dateadd(day, -DATEPART(DW, @DATE)+8, @DATE)) [last_day_of_week]
, CASE DATEPART(DW, @DATE)
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END AS [week_day_flag]
, substring(CONVERT(VARCHAR,@Date,112), 1, 6) [month_sk]
, DATEPART(MONTH,@DATE) as [month_no] --To be converted with leading zero later.
, DATENAME(MONTH,@DATE) as [month_name]
, substring(DATENAME(MONTH,@DATE), 1, 3) as [month_shortname]
, DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE) , 0) [first_day_of_month]
, DATEADD(MILLISECOND, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, @DATE) +1 , 0)) [last_day_of_month]
--Calendar quarter
, DATEPART(qq,@DATE) as [quarter_no]
, CASE DATEPART(qq,@DATE)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS [quarter_name]
, 'Q' + CAST(DATEPART(qq,@DATE) as VARCHAR(1)) [quarter_shortname]
, CONVERT (DATETIME, DATEFROMPARTS (DATEPART(YEAR,@DATE), (DATEPART(qq,@DATE) * 3) - 2, 1)) [first_day_of_quarter]
, dateadd(millisecond, -3, dateadd(month, 3, CONVERT (DATETIME, DATEFROMPARTS (DATEPART(YEAR,@DATE), (DATEPART(qq,@DATE) * 3) - 2, 1)))) [last_day_of_quarter]
, DATEPART(YEAR,@Date) as [year_no]
, DATEFROMPARTS (DATEPART(YEAR,@Date), 1, 1) [first_day_of_year]
, DATEADD(MILLISECOND, -3, CONVERT(DATETIME,DATEFROMPARTS (DATEPART(YEAR,@Date) + 1, 1, 1))) [last_day_of_year]
-- Cal Offset
, DATEDIFF(dd,@CurrentDate,@Date) as [cal_day_offset]
, DATEDIFF(ww,@CurrentDate,@Date) as [cal_week_offset]
, DATEDIFF(MONTH,@CurrentDate,@Date) as [cal_month_offset]
, DATEDIFF(qq,@CurrentDate,@Date) as [cal_quarter_offset]
, DATEDIFF(YEAR,@CurrentDate,@Date) as [cal_year_offset]
-- Op Offset
, DATEDIFF(DAY, @OpDate, @Date) [op_day_offset]
, DATEDIFF(ww,@OpDate,@Date) [op_week_offset]
, DATEDIFF(MONTH,@OpDate,@Date) [op_month_offset]
, DATEDIFF(qq,@OpDate,@Date) [op_quarter_offset]
, DATEDIFF(YEAR,@OpDate,@Date) [op_year_offset]
-- Fin Offset
, DATEDIFF(MONTH,@FinMonth,@Date) [fin_month_offset]
, DATEDIFF(qq,@FinMonth,@Date) [fin_quarter_offset]
, DATEDIFF(YEAR,@FinMonth,@Date) [fin_year_offset]
, 'FY' [fin_year_name]
, getdate() [etl_create_date]
SELECT @Date = DATEADD(dd,1,@Date)
END
September 8, 2016 at 6:39 am
Shamefully I don't use this sort of stuff and I should. I think there are numerous opportunities in our code for this.
September 8, 2016 at 7:09 am
Love the topic. Calendar tables are quite useful, and are being considered more and more for use cases beyond the data warehouse.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 8, 2016 at 7:10 am
Thanks, guys.
Jo Pattyn: Yes you could and in practice you would. Use of the constants just made the examples more readable for purposes of this article. My apologies if the MM/DD/YYYY format was alien to some readers.
Funbi: There is nothing inherently superior about either string representation of a date. What is important is that it can be converted to a date datatype.
Steven and Andrew: Obviously I didn't invent the calendar table, this article was just to reintroduce it. There can be many variations on the concept. Thanks for adding yours.
Some other things that might be useful would include having columns for fiscal year, quarter, month, day, and week. Or having columns for Canadian Holidays as well as U.S. holidays. Shameless plug: An article is coming soon that will discuss how to programatically build a holidays table to make the job of flagging holidays easier.
IWasBornReady: You're who this this article was written for. After you read the next article about holidays, you should go ahead and create your calendar table and play with it, so that you have it ready next time. It really does make date calculations much easier once the table is in place.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 7:13 am
Jo Pattyn (9/8/2016)
Great articleCould you change the where-clauses to use proper date-variables instead of strings
WHERE CalendarDate BETWEEN convert(date,'2/27/2016',101) and convert(date,'3/1/2016',101)
instead of
WHERE CalendarDate BETWEEN '2/27/2016' and '3/1/2016'
If you wanted to avoid conversion or locale concerns you could use the universal date format YYYYMMDD:
WHERE CalendarDate BETWEEN '20160227' AND '20160301'
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 8, 2016 at 7:41 am
A calendar table is really a must have in my opinion. In our business, we have to count the duration of activities for both business and non-business hours. Some of these things can begin and end on the same day, or stretch over weeks and even months in some rare cases. A function was ported from Access that would calculate the business time duration in seconds, but that uses a loop and was painfully slow. That was one of those cases where you summon your inner Jeff Moden and cut that out. I use the calendar table to fill the gap between a start and end date/times and perform the calculations that way. The process to populate a table for reporting went from round an hour and a half to less than ten minutes.
I also use this table when a report has to show date/times even if something didn't happen to report such as a monthly cross tab report with totals for actions. Joining to the calendar table avoids things like endless "if not exists". I recommend that anyone who doesn't use such a table and has loops in their code do what it takes to make the time to learn the benefits of a calendar table.
Cheers
September 8, 2016 at 7:49 am
Keep in mind that if you have a data warehouse with a date dimension, you may already have a useful calendar table.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 8, 2016 at 8:01 am
In trying the code, I got stuck immediately with a syntax error in the last line of the vTally view definition: Incorrect syntax near ')'
the parens seem to match up, so why is is it failing? I'm on SQL 2012. Is this code using a SQL construct that's supported only on later versions?
September 8, 2016 at 9:00 am
Peter, thank you for bringing this to my attention. I'm not sure how that happened but there is no excuse for it. For some reason SSC isn't letting me post the entire CREATE VIEW statement, but the last few lines should look like this
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)
SELECT TOP (10000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as N from L5
GO
My apologies.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 9:18 am
EDIT: comment removed. Missed previous comment.
September 8, 2016 at 4:11 pm
Fantastic article. Thank you!
One question, if I may, not directly related to the calendar itself, but to a use of CROSS APPLY instead on CROSS JOIN in the employee missed date query. Is there a particular reason why you used CROSS APPLY?
I changed it to CROSS JOIN and got the same result, but there must be something I'm missing.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply