CODEWITHSUNDEEP
sqlsql-server-2008datediff
Abdón Araya
Abdón Araya

Reputation: 186

Simulating NETWORKDAYS returns wrong business days

I created a NETWORKDAYS-like function on sql based on this solution which returns correctly the business days while the records (BEG and END) are on business days.

BEG        END         businessd
--------------------------------
01/01/2018 15/01/2018  10       

--(15 days total)-(1st jan)-(4 weekends) = 10 | correct result

But in some cases where BEG or END are "stored" in weekends it counts extra days:

BEG        END         businessd
--------------------------------
01/01/2018 28/01/2018  20       

--(28 days total)-(1st jan)-(4 weekends) = 19 | incorrect result

My code does a datediff between BEG and FIN and subtracting weekend days and the holydays stored on the holydays table

    SELECT e.*, 
            DATEDIFF(DAY, cast(BEG as datetime), cast(FIN as datetime))+1 --sum end date day
              -(SELECT
               (DATEDIFF(WK, cast(BEG as datetime), cast(FIN as datetime)) )
              +(CASE WHEN DATENAME(dw, cast(BEG as datetime)) = 'Sunday' THEN 1 ELSE 0 END)
              +(CASE WHEN DATENAME(dw, cast(FIN as datetime)) = 'Saturday' THEN 1 ELSE 0 END)
            ) * 2
            -(SELECT COUNT(*) FROM holydays nl
             WHERE nl.FECHA BETWEEN cast(BEG as datetime) AND cast(FIN as datetime)
            ) AS businessd,

            convert(nvarchar(6),cast(BEG as datetime),112) as iddate --new id based on fin
    FROM e
    ORDER BY original_ini,BEG

How could I handle these cases to correctly count business days?

Upvotes: 1

Views: 49

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30645

you can use calendar table for this purpose. This operation will be very easy with it.

you can even handle bank holidays

Check This and this (Calendar Table)

select * from calendar where isWorkDay = 1

will get you workdays and then you can make simple join like

select * from t
where exists 
( select 1 from calendar where isWorkDay = 1 and calendar.dt betweenn t.beg and t.end)

Upvotes: 1

Related Questions