Simulating NETWORKDAYS returns wrong business days

I created a NETWORKDAYS-like function on sql based on this solution wich 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?

Answer

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)

Advertisement

Answer