SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Calendar Table Function

By Chris Kutsch,

I have seen time and again how members of the development team go through great lengths to work with dates and calendars. There are many ways to tackle these problems, but some are far more effecient than others.

This example uses an in-line table valued function to return a calendar table that can then be consumed by the development team to simplify some of the more complex business requirements we run into.

As written, this function utilizes system tables and a persisted HOLIDAY table that is maintained by the DBA team. This HOLIDAY table contains all non-business days.

Let's say we have a business report that needs to find the last day of the month to determine a range of dates. Once the calendar function has been created, a procedure can be written to find these dates like so:

Last Day in each month:
SELECT  DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName]) AS [Date]
FROM    dbo.CALENDAR('01/01/2014','12/31/2014')
ORDER BY [Date]
Last Thursday in each month:
SELECT  DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]
FROM    dbo.CALENDAR('01/01/2014','12/31/2014')
WHERE   [WeekdayName] = 'Thursday'
ORDER BY [Date]
First and Third Monday in each month:
SELECT  c.[Date]
FROM    dbo.CALENDAR('01/01/2014','01/01/2015') AS c
        JOIN
        (   SELECT  ROW_NUMBER() OVER (PARTITION BY [Year],[MonthName],[WeekdayName] ORDER BY [RID]) AS [ID]
                ,   [MonthName]
                ,   [WeekdayName]
                ,   RID
            FROM    dbo.CALENDAR('01/01/2014','01/01/2015')
        ) l on c.RID = l.RID
WHERE   l.[WeekdayName] = 'Monday' And
        l.ID in (1,3)
ORDER BY c.RID
Bi-weekly Fridays:
SELECT  c.[Date]
FROM    dbo.CALENDAR('01/01/2014','01/01/2015') AS c
WHERE   [WeekdayName] = 'Friday' And
        WeekofYear % 2 = 0
ORDER BY RID

I hope this helps simplify some of these tougher tasks you have in your environments. I know it has in mine.

Total article views: 4572 | Views in the last 30 days: 5
 
Related Articles
FORUM

Selecting a Value of the Order Within a Group

Select To Indicate Order Number Within a Group

FORUM

select in subquery, problem

select subquery performance and order

FORUM

nth WeekDayName of a month

i.e., the 2nd Tuesday of this month

FORUM

Select top 1 from subquery

Issue with selecting row in order from subquery that's ordered

FORUM

sort order

sort order

Tags
calendar    
date    
date manipulation    
holiday    
 
Contribute