<

SQL Server Calendar Table:  Tips and Tricks

Published on
6,002 Points
1,902 Views
6 Endorsements
Last Modified:
Jim Horn
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
A collection of tips and tricks when querying dates using a calendar table.
This is second in a series of articles on dealing with custom calendars in SQL Server, and requires that you execute the code at the bottom of the SQL Server Calendar Table article. 
 
This article covers three topics:
  1. Customizable list of days 
  2. Boundaries of a range of dates, such as weeks, month, quarter, or year
  3. A list of dates with an '< ALL >' choice.
 

Customizable list of days

 
This one is a handy way to pull off a list of days with a custom definition, such as 'The least seven days, starting with whatever day yesterday is', 'The seven days after 9/1/15', and 'The three days before 12/25/15, not including that day', and is a collection of many tricks I’ve picked up.  

The below T-SQL code uses nested Common Table Expressions and these user-selectable parameters:

  • @dt                                   The date passed.  Usually today. 
  • @previous_or_next        'P' all rows are before @dt, 'N' all rows after @dt.  Sorted from @dt going outwards. 
  • @include_dt                    'Y' include the @dt day/week/whatever in the return set, 'N' do not include. 
  • @row_count                    Number of rows in the return set.

The same code is demonstrated below as both a Stored Procedure and a Table-Valued Function
  • Stored Procedure          Returns a set, Used by SSRS as a data source, 
  • Table Valued Function  Returns a set, can be JOINed to other tables or views using CROSS APPLY or OUTER APPLY.                     

Written as a Stored Procedure
 
IF EXISTS(SELECT name FROM sys.procedures WHERE name='cal_list') 
   DROP PROC cal_list
GO

CREATE PROC cal_list (
   @dt date = NULL,                  -- Start Date.  Today if a value is not passed. 
   @include_dt char(1) = 'Y',        -- Y Include @dt in the return set, N do not include
   @previous_or_next char(1) = 'P',  -- P Previous .. before @dt, N next .. after @dt
   @row_count int = 100) AS

/*
2015-09-016  Jim Horn,  Original.  http://www.jimhorn.biz  All copies of this code must include this line. 
*/

-- TESTING ONLY, comment out the below line in production
-- Declare @dt date = '20150905', @include_dt char(1) = 'Y', @previous_or_next char(1) = 'P', @row_count int = 12

-- If date was passed as NULL, use today
SET @dt = CASE WHEN @dt IS NULL THEN CAST(GETDATE() as date) ELSE @dt END

;WITH days_rows AS (
   SELECT TOP(@row_count + 1) PKDate
   FROM days
   WHERE 
      (@previous_or_next = 'P' AND PKDate <= @dt) OR 
      (@previous_or_next = 'N' AND PKDate >= @dt)
   ORDER BY 
      CASE @previous_or_next WHEN 'P' THEN PKDate END DESC, 
      CASE @previous_or_next WHEN 'N' THEN PKDate END), 
days_ranked AS (
   SELECT PKDate, RANK() OVER (ORDER BY PKDate) as row_number
   FROM days_rows), 
days_ranked_max AS (SELECT MAX(row_number) as row_number_max FROM days_ranked) 
SELECT PKDate
FROM days_ranked dr
WHERE (
   (@previous_or_next = 'P' AND @include_dt = 'Y' AND row_number >= (SELECT row_number_max FROM days_ranked_max) - @row_count + 1) OR 
   (@previous_or_next = 'P' AND @include_dt = 'N' AND row_number <> (SELECT row_number_max FROM days_ranked_max) AND row_number >= (SELECT row_number_max FROM days_ranked_max) - @row_count) OR 
   (@previous_or_next = 'N' AND @include_dt = 'Y' AND row_number <= @row_count) OR
   (@previous_or_next = 'N' AND @include_dt = 'N' AND row_number <> 1 AND row_number <= (@row_count + 1))
   ) 
ORDER BY 
   CASE @previous_or_next WHEN 'P' THEN PKDate END DESC, 
   CASE @previous_or_next WHEN 'N' THEN PKDate END
GO

Open in new window

The results...

cal_list.jpg
Written as a Function
 
IF EXISTS(SELECT name FROM sys.objects WHERE name='fn_cal_list') 
   DROP FUNCTION fn_cal_list
GO

CREATE FUNCTION fn_cal_list(
   @dt date = NULL,                  -- Start Date.  Today if a value is not passed. 
   @include_dt char(1) = 'Y',        -- Y Include @dt in the return set, N do not include
   @previous_or_next char(1) = 'P',  -- P Previous .. before @dt, N next .. after @dt
   @row_count int = 100) 
RETURNS @ret TABLE (PKDate date) 
AS

BEGIN
/*
2015-09-05  Jim Horn,  Original.  http://www.jimhorn.biz  All copies of this code must include this line. 
*/

-- TESTING ONLY, comment out the below line in production
-- Declare @dt date = '20150905', @include_dt char(1) = 'Y', @previous_or_next char(1) = 'P', @row_count int = 12

-- If date was passed as NULL, use today
SET @dt = CASE WHEN @dt IS NULL THEN CAST(GETDATE() as date) ELSE @dt END

;WITH days_rows AS (
   SELECT TOP(@row_count + 1) PKDate
   FROM days
   WHERE 
      (@previous_or_next = 'P' AND PKDate <= @dt) OR 
      (@previous_or_next = 'N' AND PKDate >= @dt)
   ORDER BY 
      CASE @previous_or_next WHEN 'P' THEN PKDate END DESC, 
      CASE @previous_or_next WHEN 'N' THEN PKDate END), 
days_ranked AS (
   SELECT PKDate, RANK() OVER (ORDER BY PKDate) as row_number
   FROM days_rows), 
days_ranked_max AS (SELECT MAX(row_number) as row_number_max FROM days_ranked) 
INSERT INTO @ret (PKDate) 
SELECT PKDate
FROM days_ranked dr
WHERE (
   (@previous_or_next = 'P' AND @include_dt = 'Y' AND row_number >= (SELECT row_number_max FROM days_ranked_max) - @row_count + 1) OR 
   (@previous_or_next = 'P' AND @include_dt = 'N' AND row_number <> (SELECT row_number_max FROM days_ranked_max) AND row_number >= (SELECT row_number_max FROM days_ranked_max) - @row_count) OR 
   (@previous_or_next = 'N' AND @include_dt = 'Y' AND row_number <= @row_count) OR
   (@previous_or_next = 'N' AND @include_dt = 'N' AND row_number <> 1 AND row_number <= (@row_count + 1))
   ) 
ORDER BY 
   CASE @previous_or_next WHEN 'P' THEN PKDate END DESC, 
   CASE @previous_or_next WHEN 'N' THEN PKDate END

RETURN
END

Open in new window


The results..

fn_cal_list.jpg


Boundaries of a range of dates, such as weeks, month, quarter, or year

 
This one is relatively easy with a calendar table, as all you have to do is query the appropriate column for the min and max date.   The below Stored Procedure performs this, and also includes parameters for the range type (week, month, quarter, year) and an offset value which will take the @dt and return the @interval range plus or minus @offset.

Most of the functionality is done with a CASE block in the WHERE clause, which combines the @interval value with an expression that uses the appropriate column. 
 
  • @dt                                   The date passed.  Usually today. 
  • @interval                         'Y' Year, 'Q' Quarter, 'M' Month, 'W' Week.  
  • @offset                            Positive number = Use @dt plus @offset number of @interval's, then get the range.  Negative number - minus. 
 
IF EXISTS(SELECT name FROM sys.procedures WHERE name='cal_boundaries') 
   DROP PROC cal_boundaries
GO

CREATE PROC cal_boundaries (@dt date, @interval char(1) = 'M', @offset int = 0) AS

/*
Get the day boundaries of a given week that contains @dt, plus or minus @offset weeks. 

2015-09-05  Jim Horn,  Original.  http://www.jimhorn.biz  All copies of this code must include this line. 
*/

-- TESTING ONLY, comment out the below line in production
-- Declare @dt date = '20150905', @interval char(1) = 'M', @offset int = 0

SELECT MIN(PKDate) as dtBegin, MAX(PKDate) as dtEnd
FROM days
WHERE 
   (@interval = 'Y' AND continuous_year = (SELECT continuous_year FROM days WHERE PKDate = DATEADD(year, @offset, @dt))) OR 
   (@interval = 'Q' AND continuous_quarter = (SELECT continuous_quarter FROM days WHERE PKDate = DATEADD(quarter, @offset, @dt))) OR 
   (@interval = 'M' AND continuous_month = (SELECT continuous_month FROM days WHERE PKDate = DATEADD(month, @offset, @dt))) OR 
   (@interval = 'W' AND continuous_week = (SELECT continuous_week FROM days WHERE PKDate = DATEADD(week, @offset, @dt)))
GO

Open in new window

The results..

exec-cal_boundaries.jpg

A list of dates with an < ALL > choice


Here’s what you need to pull this off:
  • Add a UNION ALL to your original query to include a row for the < ALL > and a value of -1.
  • Ensure correct sorting by verifying if the -1 is the first row in the number column, and if not use a separate column sort_order. 
  • Then in whatever query uses this selection handle -1 as all rows.
sort-order.jpgThe results.  The first query produces the set on the left, and the second and third query will produce the set on the right. 
  
with_without_all_side_by_side.jpgThen the Stored Procedure that accepts the @month parameter goes something like this: 
or-handles-all-months.jpg
Thank you for reading my article, feel free to leave me some feedback regarding the content or to recommend future work.  If you liked this article please click the 'Good Article' button.

I look forward to hearing from you. -  Jim - ( LinkedIn ) ( Twitter )
 
6
Comment
Author:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 51

Expert Comment

by:Mark Wills
Hi Jim,

Have been enjoying your calendar table series. Big fan of the calendar table concept.

But I think I am missing something with the "ALL" example.

1) you don't need a subquery to use order by...
select distinct calendar_month_short_name as name, calendar_month as number
from days
where calendar_year = 2015
union all
select 'ALL',-1
order by 2

Open in new window


2) if you want to order by a column name, can also use a CTE query, but column number works just as well.

3) don't understand why the union (or union all) is needed at all. It doesn't seem to be used in any of the examples.

4) in the first SP example, checking the param "OR @month = -1" will return all months for the year regardless of having the "ALL" entry.

Obviously I am missing something. Would you mind taking the time to clarify the importance of "ALL" ?
0
 
LVL 65

Author Comment

by:Jim Horn
1)  Correct.  The subquery is necessary if you can't stick the -1 in with another column and have it sort correctly, and that was in a previous version, but when I went back to having it in the number column I didn't remove that part.  Will edit.

2)  That wouldn't work for months Jan, Feb, Mar... as they wouldn't sort in calendar order.

3)  UNION ALL is needed to add the -1 row.  If there is another way without using UNION ALL, let me know.

4)  Correct, that's the intent.  If user selects <ALL> then the -1 is passed, which the SP interprets as all months.

I'll make some further improvements to this.
0
 
LVL 50

Expert Comment

by:Gustav Brock
Seems like a pro piece of work, but as I don't do T-SQL, I'm not the one to judge the subtle details.

However, I can point out - as you probably are very aware of - that your setup is very US oriented. In Europe, we follow the ISO 8601 week numbering where the week starts on Monday, a year can have 52 or 53 weeks, weeks cross the calendar year boundaries, and as both week 1 and 53 can represent very early and very late days in years, you may have to specify a week number with its corresponding year.

For example, the day of 2014-12-30 belongs to week 2015-W01.

I guess your queries etc. could be modified to accommodate this, if your week field was either expanded to hold the year information or supplemented with a field holding the year information of the year-week number.

/gustav

/gustav
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Author Comment

by:Jim Horn
Thanks Gustav.

>that your setup is very US oriented. In Europe, we follow the ISO 8601 week numbering...
Thanks for this, as being smack in the middle of the US/Canada this hasn't affected me much, hence it was not in the article.  I should probably reference this in the original article in creating the weeks.

> a year can have 52 or 53 weeks, weeks cross the calendar year boundaries
Will likely get into this in my future article on Fiscal Years.

>For example, the day of 2014-12-30 belongs to week 2015-W01.
I'll have to use '2015-W01' as the week name, as it's open to personal interpretation how to name the weeks, and I didn't have anything that really screamed out at me.
0
 
LVL 70

Expert Comment

by:Éric Moreau
Hi Jim

 I have gone (quickly) through your part 1 and 2, and the thing I would have really like is to have the cal_list SP offered as a UDF instead. We often need that kind of results as part of a resultset based on another field of the query.
0
 
LVL 65

Author Comment

by:Jim Horn
Éric  - Was this what you had in mind?  
Also let me know if I rewrite this as a function, if you think it's even worth it also showing as an SP

(air code)
-- Same cal_list but as a function
CREATE FUNCTION fn_cal_list (@dt date = NULL, @previous_or_next char(1), @include_dt char(1) = 'Y', @interval char(1), @row_count int = 100) 
RETURNS @ret TABLE (label varchar(100), value bigint, sort_order int) 
AS

-- convert T-SQL SP to fn here and RETURN THIS -->  @ret (label, value, sort_order ) 

-- Then use the function
-- Example:  Return all orders from the Sales table three days before (and including) '20150905'
SELECT s.*
FROM Sales s
   CROSS APPLY (SELECT value FROM calendar.dbo.fn_cal_list('20190905', 'P', 'Y', 'D', 3) WHERE s.OrderDate = value) val 

Open in new window

0
 
LVL 70

Expert Comment

by:Éric Moreau
Thanks Jim. Much better IMHO
0
 
LVL 6

Expert Comment

by:Yashwant Vishwakarma
Very helpful for us :) voted YES.

Regards,
Yashwant Vishwakarma
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
Looking forward saving lots of time and effort by having an article like this.

Mike
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month