sql sql-server datediff getdate date-difference

How to count number of work days and hours extracting public holidays between two dates in SQL

I am new to SQL and stuck in some complex query.

What am I trying to achieve?

I want to calculate following two types of total days between two timestamp fields.

Number of Working Days (Excluding Weekend & Public Holidays)
Number of Total Days (Including Weekend & Public Holidays)

Calculation Condition

If OrderDate time is <= 12:00 PM then start count from 0
If OrderDate Time is > 12:00 PM then start count from -1
If Delivery Date is NULL then count different till Today's Date

Data Model

OrderDate & DeliveryDate resides in 'OrderTable'
PublicHolidayDate resides 'PublicHolidaysTable'

Solution

As with many tasks in SQL, this could be solved in multiple ways.

You can use COUNT aggregate operations on the date range with the BETWEEN operator to give you aggregate totals of the weekend days and holidays from a start date (OrderDate) to an end date (DeliveryDate).

This functionality can be combined with CTEs (Common Table Expressions) to give you the end result set you are looking for.

I've put together a query that illustrates one way you could go about doing it. I've also put together some test data and results to illustrate how the query works.

DECLARE @DateRangeBegin DATETIME = '2016-01-01'
      , @DateRangeEnd   DATETIME = '2016-07-01'

DECLARE @OrderTable TABLE 
(OrderNum INT, OrderDate DATETIME, DeliveryDate DATETIME)

INSERT INTO @OrderTable VALUES
  (1, '2016-02-12 09:30', '2016-03-01 13:00')
, (2, '2016-03-15 13:00', '2016-03-30 13:00')
, (3, '2016-03-22 14:00', NULL)
, (4, '2016-05-06 10:00', '2016-05-19 13:00')

DECLARE @PublicHolidaysTable TABLE 
(PublicHolidayDate DATETIME, Description NVARCHAR(255))

INSERT INTO @PublicHolidaysTable VALUES 
  ('2016-02-15', 'President''s Day')
, ('2016-03-17', 'St. Patrick''s Day')
, ('2016-03-25', 'Good Friday')
, ('2016-03-27', 'Easter Sunday')
, ('2016-05-05', 'Cinco de Mayo')

Some considerations you may of already thought of are that you don't want to count both weekend days and holidays that occur on a weekend, unless your company observes the holiday on the next Monday. For simplicity, I've excluded any holiday that occurs on a weekend day in the query.

You'll also want to limit this type of query to a specific date range.

The first CTE (cteAllDates) gets all dates between the start and end date range.

The second CTE (cteWeekendDates) gets all weekend dates from the first CTE (cteAllDates).

The third CTE (ctePublicHolidays) gets all holidays that occur on weekdays from your PublicHolidaysTable.

The last CTE (cteOrders) fulfills the requirement that the count of total days and working days must begin from the next day if the OrderDate is after 12:00PM and the requirement that the DeliveryDate should use today's date if it is null.

The select statement at the end of the CTE statements gets your total day count, weekend count, holiday count, and working days for each order.

;WITH cteAllDates AS (
SELECT 1 [DayID]
     , @DateRangeBegin [CalendarDate]
     , DATENAME(dw, @DateRangeBegin) [NameOfDay]
UNION ALL
SELECT cteAllDates.DayID + 1 [DayID]
     , DATEADD(dd, 1 ,cteAllDates.CalendarDate) [CalenderDate]
     , DATENAME(dw, DATEADD(d, 1 ,cteAllDates.CalendarDate)) [NameOfDay]
FROM cteAllDates
WHERE DATEADD(d,1,cteAllDates.CalendarDate) < @DateRangeEnd
)
, cteWeekendDates AS (
SELECT CalendarDate
FROM cteAllDates
WHERE NameOfDay IN ('Saturday','Sunday')
)
, ctePublicHolidays AS (
SELECT PublicHolidayDate
FROM @PublicHolidaysTable
WHERE DATENAME(dw, PublicHolidayDate) NOT IN ('Saturday', 'Sunday')
)
, cteOrders AS (
SELECT OrderNum
     , OrderDate
     , CASE WHEN DATEPART(hh, OrderDate) >= 12 THEN DATEADD(dd, 1, OrderDate) 
            ELSE OrderDate 
       END [AdjustedOrderDate]
     , CASE WHEN DeliveryDate IS NOT NULL THEN DeliveryDate 
            ELSE GETDATE() 
       END [DeliveryDate]
FROM @OrderTable
)
SELECT o.OrderNum
     , o.OrderDate
     , o.DeliveryDate
     , DATEDIFF(DAY, o.AdjustedOrderDate, o.DeliveryDate) [TotalDayCount]
     , (SELECT COUNT(*) FROM cteWeekendDates w 
        WHERE w.CalendarDate BETWEEN o.AdjustedOrderDate AND o.DeliveryDate) [WeekendDayCount]
     , (SELECT COUNT(*) FROM ctePublicHolidays h 
        WHERE h.PublicHolidayDate BETWEEN o.AdjustedOrderDate AND o.DeliveryDate) [HolidayCount]
     , DATEDIFF(DAY, o.AdjustedOrderDate, o.DeliveryDate)
       - (SELECT COUNT(*) FROM cteWeekendDays w 
          WHERE w.CalendarDate BETWEEN o.AdjustedOrderDate AND o.DeliveryDate)
       - (SELECT COUNT(*) FROM ctePublicHolidays h 
          WHERE h.PublicHolidayDate BETWEEN o.AdjustedOrderDate AND o.DeliveryDate) [WorkingDays]
FROM cteOrders o
WHERE o.OrderDate BETWEEN @DateRangeBegin AND @DateRangeEnd
OPTION (MaxRecursion 500)

Results from the above query using the test data...

What I'd probably do is simplify the above by adding a Calendar table populated with sufficiently wide date ranges. Then I'd take some of the CTE statements and turn them into views.

I think specifically valuable to you would be a view that gets you the work days without weekends or holidays. Then you could just simply get the date difference between the two dates and count the work days in the same range.