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

回答1:

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)

回答2:

Kajiyama,

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

Looks like you were missing the SELECT statement before the COUNT(*)

回答3:

Here is the diffrent answer with WITH common_table_expression (CTE)

;with  t as
(
select COUNT(*) as cnt FROM public_holidays 
WHERE date_from BETWEEN evnt.date_from AND evnt.date_to
)
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 cnt from T)
       FROM events AS evnt

回答4:

( I see this is already answered, but I'll throw this out anyways...)

Might not be a bad idea to have a calendar table with each day as a row, as opposed to a separate public_holidays table. Check out the article SQL Server Calendar Table for a demo and downloadable T-SQL code. Includes a couple of follow-on articles for querying and fiscal years.

回答5:

I saw some examples elsewhere like the below.

If you're looking for a 'business day diff' then please also compare it to a normal calendar day diff (i.e. DATEDIFF). Diff between two adjacent weekdays should be 1 day in both cases. Other solutions result in two business days between say Tuesday and Wednesday.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '1/17/19'
SET @EndDate = '1/18/19'

-- CalendarDateDiff vs Business Date Diff
SELECT
    DATEDIFF(d, @StartDate, @EndDate) AS CalendarDateDiff
    , (DATEDIFF(dd, @StartDate, @EndDate) + 1) 
    -1
    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
    -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
    -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS CalendarDays