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