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 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)