Search code examples
t-sqldate-rangedate-difference

SQL DateDiff without weekends and public holidays

I am looking for solution how to select number of days between two dates without weekends and public holidays.

So far I have this:

SELECT evnt.event_id,
       evnt.date_from,
       evnt.date_to,
       DATEDIFF(DD, evnt.date_from, evnt.date_to) 
       - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
       - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
       + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
       --- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to
       FROM events AS evnt

everything works fine untill I uncomment section: 

- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to

What I am trying to achieve is to get number of working days in date range. Problem is with last step, where I am trying to substract all public holiday days from this range.

Can anyone help with this last step? It seems, that I am doing something wrong, but I cant figure out what.

Thank you in advance

Solution

  • Try This:

    SELECT evnt.event_id,
   evnt.date_from,
   evnt.date_to,
   DATEDIFF(DD, evnt.date_from, evnt.date_to) 
   - (DATEDIFF(WK, evnt.date_from, evnt.date_to) * 2) 
   - CASE WHEN DATEPART(DW, evnt.date_from) = 1 THEN 1 ELSE 0 END 
   + CASE WHEN DATEPART(DW, evnt.date_to) = 1 THEN 1 ELSE 0 END AS Date_Diff
   - (SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)
   FROM events AS evnt

    the uncomment should be a Subquery

    --- COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to

    like this:

    - (SELECT COUNT(*) FROM public_holidays AS h WHERE h.date_from BETWEEN evnt.date_from AND evnt.date_to)