Funkcja zliczająca dni robocze w SQL Server

Jeśli w SQL Server brakuje jakiejś ważnej funkcji, z pewnością jest to m.in. funkcja pozwalająca nam zliczać dni robocze pomiędzy datami. Poniżej prosty przykład takiej funkcji. Uwzględnia ona takie warunki jak:

Zwraca różnicę pomiędzy dwiema datami w liczbach całkowitych
Pomija weekendy i święta (w naszym przypadku święta dla lat 2015 i 2016).
Jeśli daty, będące argumentami funkcji są dniami świątecznymi lub weekendowymi – są pomijane. Data „Od” przyjmuje wartość kolejnego dnia roboczego.
Jeśli data „od” oraz „do” są sobie równe, wynikiem funkcji będzie 0. Jeśli data „od” oraz „do” to dwa kolejne dni robocze- wynikiem funkcji będzie 1
W przypadku, gdy data „od” jest późniejsza od daty „do” – wynikiem funkcji będzie 0. Nie pokazujemy wyników w formie ujemnej.
W przypadku, gdy brakuje któregoś z argumentów funkcji – wynikiem będzie NULL.

CREATE function [dbo].[TK_WORK_DAYS_INT] 
(@V_START DATETIME,
@V_END DATETIME)
RETURNS INT
AS
begin
declare @START_DT as DATETIME
declare @END_DT as DATETIME
declare @DATE_DIFF as INT
declare @COUNTER AS INT

--SZYBKI ZWROT FUNKCJI W PRZYPADKU GDY 1 LUB 2 ARGUMENTY FUNKCJI SA PUSTE
 IF (@V_START IS NULL OR @V_END IS NULL)
 BEGIN 
 RETURN NULL
 END

SET @START_DT = CAST(@V_START AS DATE) --TRUNC NA DACIE
SET @END_DT = CAST(@V_END AS DATE) --TRUNC NA DACIE
SET @COUNTER = 0

--USTAWIANIE START DATE NA NAJBLIZSZY DZIEN ROBOCZY
 while (DATEPART(DW, @START_DT) IN ('1','7') OR @START_DT IN ('2015-01-01', '2015-01-06', '2015-04-06', '2015-05-01', '2015-06-04',
 '2015-11-11', '2015-12-24', '2015-12-25', '2016-01-01', '2016-01-06', '2016-03-28',
 '2016-05-03', '2016-05-26', '2016-08-15','2016-11-01', '2016-11-11', '2016-12-26'))
 BEGIN
 SET @START_DT = DATEADD(D, 1, @START_DT)
 END

--USTAWIANIE ROZNICY W DATACH OD PIERWSZEGO DNIA ROBOCZEGO DLA START DO END
 SET @DATE_DIFF = CONVERT(INT,@END_DT) - CONVERT(INT,@START_DT) 
 
--WARUNEK, DLA END_DT W WEEKEDNY I SWIETA - USTAWIA DATE I CZAS ROWNY START. ELIMINUJE WARTOSCI UJEMNE W POZNIEJSZYM LICZENIU
 IF @START_DT > @END_DT
 BEGIN
 RETURN 0
 END

--ZLICZANIE DNI WEEKENDOWYCH I SWIATECZNYCH
 WHILE @START_DT < @END_DT
 BEGIN
 IF (DATEPART(DW, @START_DT) IN ('1','7') OR CONVERT(VARCHAR(10), @START_DT,20) IN ('2015-01-01', '2015-01-06', '2015-04-06', '2015-05-01', '2015-06-04',
 '2015-11-11', '2015-12-24', '2015-12-25', '2016-01-01', '2016-01-06', '2016-03-28',
 '2016-05-03', '2016-05-26', '2016-08-15','2016-11-01', '2016-11-11', '2016-12-26'))
 BEGIN 
 --SET @START_DT = DATEADD(D, 1, @START_DT)
 SET @COUNTER = @COUNTER+1
 END
 SET @START_DT = DATEADD(D, 1, @START_DT)
 END

--ROZNICA W DNIACH CALKOWITYCH

 RETURN @DATE_DIFF - @COUNTER

END
GO

Teraz możemy sprawdzić działanie naszej funkcji następującym kodem:

SELECT DBO.TK_WORK_DAYS('2016-01-01','2016-01-19');

Wynikiem naszej funkcji w tym wypadku będzie:
———–
10

(1 row(s) affected)

Rozbudujmy naszą funkcję. Chcemy, uzupełnić nasze warunki o kolejne:

Dodajemy kolejny argument funkcji określający, czy wynik funkcji chcemy widzieć w formie liczb całkowitych, czy też w liczbach po przecinku. 0-wynik w liczbach całkowitych 1-wynik w formie liczb po przecinku (FLOAT)

CREATE&nbsp;function [dbo].[TK_WORK_DAYS]
(@V_START DATETIME,
@V_END DATETIME,
@V_AIM AS INT)
RETURNS FLOAT
AS
begin
declare @START_DT as DATETIME
declare @END_DT as DATETIME
declare @START_TM as DATETIME
declare @END_TM as DATETIME
declare @DATE_DIFF as INT
declare @TIME_DIFF AS FLOAT
declare @COUNTER AS INT

--SZYBKI ZWROT FUNKCJI W PRZYPADKU GDY 1 LUB 2 ARGUMENTY FUNKCJI SA PUSTE
IF (@V_START IS NULL OR @V_END IS NULL)
BEGIN
RETURN NULL
END

SET @START_DT = CAST(@V_START AS DATE) --TRUNC NA DACIE
SET @END_DT = CAST(@V_END AS DATE) --TRUNC NA DACIE
SET @START_TM = CAST(@V_START AS TIME) -- sama godzina ze start_dt
SET @END_TM = CAST(@V_END AS TIME) -- sama godzina ze start_dt
SET @COUNTER = 0

--USTAWIANIE START DATE NA NAJBLIZSZY DZIEN ROBOCZY
while (DATEPART(DW, @START_DT) IN ('1','7') OR @START_DT IN ('2015-01-01', '2015-01-06', '2015-04-06', '2015-05-01', '2015-06-04',
'2015-11-11', '2015-12-24', '2015-12-25', '2016-01-01', '2016-01-06', '2016-03-28',
'2016-05-03', '2016-05-26', '2016-08-15','2016-11-01', '2016-11-11', '2016-12-26'))
BEGIN
SET @START_DT = DATEADD(D, 1, @START_DT)
END

--USTAWIANIE ROZNICY W DATACH OD PIERWSZEGO DNIA ROBOCZEGO DLA START DO END
SET @DATE_DIFF = CONVERT(INT,@END_DT) - CONVERT(INT,@START_DT)

--WARUNEK, DLA END_DT W WEEKEDNY I SWIETA - USTAWIA DATE I CZAS ROWNY START. ELIMINUJE WARTOSCI UJEMNE W POZNIEJSZYM LICZENIU
IF @START_DT > @END_DT
BEGIN
RETURN 0
END

--ZLICZANIE DNI WEEKENDOWYCH I SWIATECZNYCH
WHILE @START_DT < @END_DT
BEGIN
IF (DATEPART(DW, @START_DT) IN ('1','7') OR CONVERT(VARCHAR(10), @START_DT,20) IN ('2015-01-01', '2015-01-06', '2015-04-06', '2015-05-01', '2015-06-04',
'2015-11-11', '2015-12-24', '2015-12-25', '2016-01-01', '2016-01-06', '2016-03-28',
'2016-05-03', '2016-05-26', '2016-08-15','2016-11-01', '2016-11-11', '2016-12-26'))
BEGIN
--SET @START_DT = DATEADD(D, 1, @START_DT)
SET @COUNTER = @COUNTER+1
END
SET @START_DT = DATEADD(D, 1, @START_DT)
END

--ROZNICA W DNIACH CALKOWITYCH
IF @V_AIM = 0
BEGIN
RETURN @DATE_DIFF - @COUNTER
END

--SETOWANIE GODZINY STARTU NA 8:00, GDY START W DZIEN SWIATECZNY BADZ WEEKEND
IF (DATEPART(DW, @START_DT) IN ('1','7') OR @START_DT IN ('2015-01-01', '2015-01-06', '2015-04-06', '2015-05-01', '2015-06-04',
'2015-11-11', '2015-12-24', '2015-12-25', '2016-01-01', '2016-01-06', '2016-03-28',
'2016-05-03', '2016-05-26', '2016-08-15','2016-11-01', '2016-11-11', '2016-12-26'))
BEGIN
SET @START_TM = '08:00:00'
END

--OBLICZANIE ROZNICY W GODZINACH
SET @TIME_DIFF = CONVERT(FLOAT, DATEDIFF(SS, @START_TM, @END_TM)) / 86000

--ZWROT GDY WYNIK PONIZEJ ZERA
IF (@DATE_DIFF - @COUNTER) + @TIME_DIFF < 0
BEGIN
RETURN 0
END

--ZWROT Z DATA I GODZINAMI
RETURN (@DATE_DIFF - @COUNTER) + @TIME_DIFF

END
GO

Na sam koniec sprawdzamy wynik naszej funkcji:

SELECT DBO.TK_WORK_DAYS('2016-01-01','2016-01-19 12:00:00', 1)
;

———————-
10,5023255813953

(1 row(s) affected)

Dodaj komentarz Anuluj pisanie odpowiedzi