THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
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 DATEFIRST 7  -- Default is 7 (US / Sunday) 


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)
             ) ,
          AS ( SELECT   YYYY = @StartYear
               UNION ALL
               SELECT   YYYY + 1
               FROM     Years
               Where    YYYY < @EndYear
             ) ,
          AS ( SELECT   MM = 1
               UNION ALL
               SELECT   MM + 1
               FROM     Months
               WHERE    MM < 12
             ) ,
          AS ( SELECT   DD = 1
               UNION ALL
               SELECT   DD + 1
               FROM     Days
               WHERE    DD < 31
             ) ,
          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)

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
WHEN recurring.Date IS NULL AND fixed.Date IS NULL THEN 1
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:



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!


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:

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 ;-)


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

Raffaele said:

Ciao potresti darmi le specifiche delle tabelle necessarie?

January 11, 2016 9:00 AM

Marco Russo (SQLBI) said:

Raffaele, the example doesn't require any existing table. Just run it.

January 11, 2016 9:05 AM
New Comments to this post are disabled

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



Privacy Statement