Fun with Dates (M_TIME_DIMENSION): WorkDays, Holidays , Federal holidays, Weekly holidays etc

Fun with Dates (M_TIME_DIMENSION): WorkDays, Holidays , Federal holidays, Weekly holidays etc: In this blog, I will be working with M_TIME_DIMENSION to make it easier to calculate number of working days between two dates or number of holidays between two dates etc

Project Requirement:

Calculate number of work days between two dates.

Issue

1. To calculate all the federal holidays as well as weekly holidays (USA specific)
2. To calculate number of work days

Solution

Presumption: M_TIME_DIMENSION  is already created.

Alter M_TIME_DIMENSION  add column Holiday below script takes into account following caveat

Holidays that fall on a Saturday are observed by federal employees who work a standard Monday to Friday week on the previous Friday whereas Holidays that fall on a Sunday are observed on following Monday.

ALTER Table "_SYS_BI"."M_TIME_DIMENSION" ADD ("HOLIDAYS" NVARCHAR(100) GENERATED ALWAYS AS case --New Year Day when month = '01' and Day = '01' and DAYNAME(DATE_SQL) not in( 'SATURDAY','SUNDAY') Then 'FE-New Years Day' when month = '12' and Day = '31'and DAYNAME(DATE_SQL) = 'FRIDAY' Then 'FE-New Years Day' when month = '01' and Day = '02' and DAYNAME(DATE_SQL) = 'MONDAY' Then 'FE-New Years Day' -- Martin Luther King, Jr when month = '01' and Day between '15' and '21' and DAYNAME(DATE_SQL) ='MONDAY' Then 'FE-Martin Luther King, Jr' --George Washington’s Birthday February 15–21 (Floating Monday) when month = '02' and Day between '15' and '21' and DAYNAME(DATE_SQL) ='MONDAY' Then 'FE-George Washington’s Birthday' --Memorial Day May 25–31 (Floating Monday) when month = '05' and Day between '15' and '21' and DAYNAME(DATE_SQL) ='MONDAY' Then 'FE-Memorial Day' --Independence Day July 4 (FIXED) when month = '07' and Day = '04' and DAYNAME(DATE_SQL) not in( 'SATURDAY','SUNDAY') Then 'FE-Independence Day' when month = '07' and Day = '03'and DAYNAME(DATE_SQL) = 'FRIDAY' Then 'FE-Independence Day' when month = '07' and Day = '05' and DAYNAME(DATE_SQL) = 'MONDAY' Then 'FE-Independence Day' --Labor Day September 1–7 (Floating Monday) when month = '09' and Day between '01' and '07' and DAYNAME(DATE_SQL) ='MONDAY' Then 'FE-Labor Day' --Columbus Day October 8–14 (Floating Monday) when month = '10' and Day between '08' and '14' and DAYNAME(DATE_SQL) ='MONDAY' Then 'FE-Columbus Day' --Veterans Day November 11 (Fixed) when month = '11' and Day = '11' and DAYNAME(DATE_SQL) not in( 'SATURDAY','SUNDAY') Then 'FE-Veterans Day' when month = '11' and Day = '10' and DAYNAME(DATE_SQL) = 'FRIDAY' Then 'FE-Veterans Day' when month = '11' and Day = '12' and DAYNAME(DATE_SQL) = 'MONDAY' Then 'FE-Veterans Day' --Thanksgiving Day November 22–28 (Floating Thursday) when month = '11' and Day between '22' and '28' and DAYNAME(DATE_SQL) ='THURSDAY' Then 'FE-Thanksgiving Day' --Christmas Day December 25 (Fixed) when month = '12' and Day = '25' and DAYNAME(DATE_SQL) not in( 'SATURDAY','SUNDAY') Then 'FE-Christmas Day' when month = '12' and Day = '24'and DAYNAME(DATE_SQL) = 'FRIDAY' Then 'FE-Christmas Day' when month = '12' and Day = '26' and DAYNAME(DATE_SQL) = 'MONDAY' Then 'FE-Christmas Day' -- Weekly Holidays when DAYNAME(DATE_SQL) in( 'SATURDAY','SUNDAY') Then 'WE-Weekly Holiday' else 'WD-Work Day' end);

[Note: Keep the Alter statement copy as a backup so that if M_TIME_DIMENSION table is re-created you can run your Alter statement]

There will be new column Holidays in M_TIME_DIMENSION  table.

-- Current_Date is 05/15/2017 --No of Days select count(*) from "M_TIME_DIMENSION" where DATE_SQL between to_date('01-01-2017','MM-DD-YYYY') and current_date; --Result --135 -- Calculate Holidays select count(*) from "M_TIME_DIMENSION" where DATE_SQL between to_date('01-01-2017','MM-DD-YYYY') and current_date and left(HOLIDAYS,2) != 'WD'; --Result --43 -- Calculate Work Days select count(*) from "M_TIME_DIMENSION" where DATE_SQL between to_date('01-01-2017','MM-DD-YYYY') and current_date and left(HOLIDAYS,2) = 'WD'; --Result --92 -- Calculate Federal Holidays select count(*) from "M_TIME_DIMENSION" where DATE_SQL between to_date('01-01-2017','MM-DD-YYYY') and current_date and left(HOLIDAYS,2) = 'FE'; --Result --4

There are other solutions as well and you can write stored procedures, function or calculated column in your view but once you have a pre-calculated column for holidays performance improves compared to other solutions.