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.

You can add more Holidays as per your project demands.

Result

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