THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in SQLBI - Marco Russo (Entire Site) Search

## SQLBI - Marco Russo

Please visit the same blog at https://www.sqlbi.com/blog/marco/ - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).

# 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:

#### 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

#### 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

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.