CODEWITHSUNDEEP
sqlsql-servert-sql
Christopher Kinyua
Christopher Kinyua

Reputation: 170

Datediff excluding weekends

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:

enter image description here

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

Upvotes: 1

Views: 1144

Answers (1)

Morten
Morten

Reputation: 414

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

Upvotes: 1

Related Questions