Datediff excluding weekends

Question

I have a query that calculates the turn-around-time, difference in days, between two dates. The dates are in a business process for approval of applications.

Therefore, if the first user approves an application on Friday and the next user approves on Monday, the expected TAT is 1.

I have managed to achieve this result apart from in a rare scenario where both approvals are done on a weekend which returns a TAT of -1 as shown below:

I would like this to return a TAT of 0.

The following is my datediff calculation:

SELECT STEP_ONE, STEP_TWO,

(DATEDIFF(dd, STEP_ONE,STEP_TWO))
  -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
  -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
  -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
  AS TAT 

FROM TEST_1

Excluding only weekends doesn't work for business purposes. What about bank holidays? The typical way this is handled is to create a Calendar table with one row per day for the next N years, with fields for year, month, week number, day etc and flags that determine whether it's a working day, holiday, weekend etc. That way calculatin working days can be as simple as a `SELECT COUNT(*) From Calendar where date between @start and @end and IsWorking=1`. — Panagiotis Kanavos, Mar 29 '19 at 08:09
Possible duplicate of [Dates difference excluding weekends](https://stackoverflow.com/questions/8284622/dates-difference-excluding-weekends) — Diado, Mar 29 '19 at 09:18

score 1 · Accepted Answer · edited Apr 09 '19 at 07:30

This method is checking if TAT is negative, and then returning 0 instead if that is the case:

SELECT STEP_ONE, STEP_TWO,

IIF (
        (DATEDIFF(dd, STEP_ONE,STEP_TWO))
        -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
        -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
        -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
        <= 0,
        0,
        (DATEDIFF(dd, STEP_ONE,STEP_TWO))
        -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
        -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
        -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
    ) AS TAT 

FROM TEST_1

The two long expressions of 4 lines each are identical to the one you wrote.

EDIT: To make the condition only affect TATs that were computed on weekends (as you asked for in your comment below) you can wrap the IIF inside another IIF that asks about weekends. Here is the resulting even longer query:

SELECT STEP_ONE, STEP_TWO,

IIF (@@DATEFIRST = 1 AND (DatePart(dw, STEP_ONE) > 5 OR DatePart(dw, STEP_TWO) > 5)
  OR @@DATEFIRST = 7 AND (DatePart(dw, STEP_ONE) IN (1, 7) OR DatePart(dw, STEP_TWO) IN (1, 7)),

    IIF (
            (DATEDIFF(dd, STEP_ONE,STEP_TWO))
            -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
            -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
            -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
            <= 0,
            0,
            (DATEDIFF(dd, STEP_ONE,STEP_TWO))
            -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
            -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
            -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end
        )),
    (DATEDIFF(dd, STEP_ONE,STEP_TWO))
    -(DATEDIFF(wk, STEP_ONE,STEP_TWO) * 2)
    -(case datepart(dw, STEP_ONE)+@@datefirst when 8 then 1 else 0 end) 
    -(case datepart(dw, STEP_TWO)+@@datefirst when 7 then 1 when 14 then 1 else 0 end)
) AS TAT 

FROM TEST_1

Hi. Thank you for your solution But is there a way of setting this condition to only affect TATs that were computed on weekends? — Christopher Kinyua, Mar 29 '19 at 10:01

Datediff excluding weekends

1 Answers1