THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Generate date dimension that support working days calculation

I just wrote a T-SQL query based on CTE that generates support information to calculate working days in a period. Simply look at the WorkingDaySequential measure, which difference between two dates is the number of elapsed working days.

In the real world:

  • The Holidays CTE could be replaced by a real table with user-inserted data.
  • The temporary [#Calendar] could be a real table, providing necessary data access from DM

and probably much else.

The following code is provided "as is", without any warranty about its behavior.

UPDATE 2007-11-28 - fixed two missing conditions in the query

SET STATISTICS IO ON
GO

SET DATEFIRST 7  -- Default is 7 (US / Sunday) 
GO

DECLARE @StartYear AS INT 
DECLARE @EndYear AS INT 

SET @StartYear = 2006; 
SET @EndYear = 2015; 

WITH    Holidays
          AS ( -- Italian Holidays (use year 1900 for recurrencies dates)
               SELECT   Date = CAST('19000101' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000106' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000425' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000501' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000602' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19000815' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19001101' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19001208' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19001225' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('19001226' AS DATETIME)
               UNION ALL
       -- Holidays changing date each year
               SELECT   Date = CAST('20060417' AS DATETIME)
               UNION ALL
               SELECT   Date = CAST('20070409' AS DATETIME)
             ) ,
        Years
          AS ( SELECT   YYYY = @StartYear
               UNION ALL
               SELECT   YYYY + 1
               FROM     Years
               Where    YYYY < @EndYear
             ) ,
        Months
          AS ( SELECT   MM = 1
               UNION ALL
               SELECT   MM + 1
               FROM     Months
               WHERE    MM < 12
             ) ,
        Days
          AS ( SELECT   DD = 1
               UNION ALL
               SELECT   DD + 1
               FROM     Days
               WHERE    DD < 31
             ) ,
        DatesRaw
          AS ( SELECT   YYYY = YYYY,
                        MM = MM,
                        DD = DD,
                        ID_Date = YYYY * 10000 + MM * 100 + DD,
                        DateString = CAST(YYYY * 10000 + MM * 100 + DD AS VARCHAR),
                        Date = CASE WHEN ISDATE(YYYY * 10000 + MM * 100 + DD) = 1
                                    THEN CAST(CAST(YYYY * 10000 + MM * 100 + DD AS VARCHAR) AS DATETIME)
ELSE NULL

END FROM Years CROSS JOIN Months CROSS JOIN Days WHERE ISDATE(YYYY * 10000 + MM * 100 + DD) = 1 ) SELECT DatesRaw.*, DayOfWeek = DATEPART(dw, DatesRaw.Date), CalendarDaySequential = CAST(DatesRaw.Date AS INT), WorkingDay = CAST(CASE DATEPART(dw, DatesRaw.Date) WHEN 1 THEN 0 -- Sunday WHEN 7 THEN 0 -- Saturday
ELSE CASE
WHEN recurring.Date IS NULL AND fixed.Date IS NULL THEN 1
ELSE 0
END
END AS BIT) INTO #Calendar FROM DatesRaw LEFT JOIN Holidays recurring ON recurring.Date = DATEADD(Year, 1900 - YEAR(DatesRaw.Date), DatesRaw.Date) LEFT JOIN Holidays fixed ON fixed.Date = DatesRaw.Date GO

-----------------------------------------------------------------------------------------------------------

SELECT *, WorkingDaySequential = ( SELECT COUNT(WorkingDay) FROM #Calendar wd3 WHERE wd3.CalendarDaySequential <= wd1.CalendarDaySequential AND wd3.WorkingDay = 1 ) FROM #Calendar wd1 ORDER BY ID_Date GO
 
Published Wednesday, November 28, 2007 12:45 AM by Marco Russo (SQLBI)
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Simon Worth said:

I actually can't get the script to run - I'm gonna have a closer look and see what the issue is, but once working - it looks like this could be very productive.  Nice work.

November 28, 2007 1:17 PM
 

Simon Worth said:

Ahhh, OK, looks like

"WHEN recirrung.Date IS NULL AND fixed.Date IS NULL THEN 1" has a typo in it.  Should be "recurring.Date"

And added a semi-colon after the variable declarations.

Now it's working well!  Very cool script.

November 28, 2007 1:34 PM
 

Marco Russo (SQLBI) said:

Thank you Simon, I corrected the post.

I had to change a little bit my original script that was customized for a particular usage and I made some mistake... thank you again!

Marco

November 28, 2007 4:11 PM
 

IDisposable said:

I've got a SQL Server 2000 (and others) compatible way of generating a date range that doesn't need CTEs and is VERY quick documented here:

http://musingmarc.blogspot.com/2006/07/need-date-range-in-sql-without-filling.html

You could use that in place of all the stuff generating the DatesRaw without creating "bogus" dates that have to be filtered....

November 29, 2007 3:13 AM
 

Rumtata said:

Nice, thanks a lot!

Just one little annotation:

The WorkingDay calculation doesn't work anymore when changing DATEFIRST.

After changing DATEFIRST to 1 (this is default for Germany) tomorrow (2007-12-01) shouldn't be a working day...

Hopefully me boss won't read this ;-)

Karsten

November 30, 2007 4:53 AM
 

Marco Russo (SQLBI) said:

I used "SET DATEFIRST 7 " because I made some assumption into the code. If you want to use another setting for DATEFIRST, you have to change the CASE clause used in WorkingDay assignment.

November 30, 2007 8:08 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement