USE [SISIMPU]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- menghitung jumlah hari kerja (senin sampai jumat) dikurangi hari libur yang terdapat di tabel [Master].DayOff
-- by Pribadi Pamungkas, 2021-08-10

CREATE FUNCTION [Master].FN_GetWeekDays
(
    @startdate DATE,
    @enddate DATE
)
RETURNS INT
AS
BEGIN
    DECLARE @jumlah_hari INT

    SELECT
        @jumlah_hari = DATEDIFF(dd, @startdate, @enddate) 
                       - DATEDIFF(wk, @startdate, @enddate) * 2 
                       - CASE
                             WHEN DATEPART(dw, @startdate) = 1 THEN 1
                             ELSE 0
                         END 
                       + CASE
                             WHEN DATEPART(dw, @enddate) = 1 THEN 1
                             ELSE 0
                         END
                       - (SELECT COUNT(*) FROM [Master].DayOff WHERE DayOffDate BETWEEN @startdate AND @enddate AND DayOffStatus = 1)
                       + 1	-- menyertakan hari pada startdate dan enddate

    RETURN @jumlah_hari

END
GO

