W jaki sposób w SQL-u obliczyć ilość dni roboczych pomiędzy dwiema datami, uwzględniając weekendy oraz święta w Polsce?
W jaki sposób w SQL-u obliczyć ilość dni roboczych pomiędzy dwiema datami, uwzględniając weekendy oraz święta w Polsce?
Niestety, SQL Server nie posiada gotowej funkcji. Musimy taką funkcję napisać sami.
Na podstawie algorytmu Jeana Meeusa Butchera opisanej tutaj utworzyłem funkcję o nazwie GetEaster w języku TSQL, która oblicza datę Wielkanocy dla podanego roku.
create function dbo.GetEaster(@year int)
returns Date
begin
declare @Easter date declare @a int, @b int, @c int, @d int, @e int, @f int, @g int, @h int, @i int, @k int, @l int, @m int, @p int, @n int
set @a = @year % 19 set @b = @year / 100
set @c = @year % 100
set @d = @b / 4 set @e = @b % 4
set @f = (@b + 8) / 25 set @g = (@b - @f + 1) / 3 set @h = (19 * @a + @b - @d - @g + 15) % 30
set @i = @c / 4 set @k = @c % 4
set @l = (32 + 2*@e + 2*@i - @h - @k) % 7
set @m = (@a + 11 * @h + 22 * @l) / 451
set @p = (@h + @l - 7 * @m + 114) % 31
set @p = @p + 1
set @n = (@h + @l - 7 * @m + 114) / 31
set @Easter = DATEFROMPARTS(@year, @n, @p)
return @Easter end
GO
Następnie tej funkcji możemy użyć w funkcji GetHolidays, która zwraca listę dni świątecznych w podanym roku. Funkcja zwraca listę dni wolnych od pracy na podany rok.
create function dbo.GetHolidays(@year int)
returns @Dates table (HolidayDate date, HolidayName nvarchar(100))
as begin
declare @Easter date
set @Easter = dbo.GetEaster(@year)
insert into @Dates values
(DATEFROMPARTS(@year, 1, 1), 'Nowy Rok'),
(DATEFROMPARTS(@year, 1, 6), 'Trzech Króli (Objawienie Pańskie)'),
(@Easter, 'Wielkanoc'),
(dateadd(day, 1, @Easter), 'Poniedziałek Wielkanocny'),
(DATEFROMPARTS(@year, 5, 1), 'Międzynarodowe Święto Pracy'),
(DATEFROMPARTS(@year, 5, 3), 'Święto Konstytucji 3 Maja'),
(dateadd(day, 60, @Easter), 'Boże Ciało'),
(DATEFROMPARTS(@year, 8, 15), 'Święto Wojska Polskiego, Wniebowzięcie Najświętszej Maryi Panny'),
(DATEFROMPARTS(@year, 11, 1), 'Wszystkich Świętych'),
(DATEFROMPARTS(@year, 11, 11), 'Narodowe święto Niepodległości'),
(DATEFROMPARTS(@year, 12, 25), 'Boże Narodzenie (pierwszy dzień)'),
(DATEFROMPARTS(@year, 12, 26), 'Boże Narodzenie (drugi dzień)')
return
end
GO
Zwracam na uwagę na zastosowanie Table-Valued Function.
Mając taką listę możemy wreszcie przystąpić do napisania funkcji, która oblicza ilość dni roboczych (z pominięciem świąt i weekendów).
CREATE FUNCTION dbo.WorkingDays ( @StartDate datetime, @EndDate datetime)
RETURNS int
AS BEGIN
DECLARE @days int
DECLARE @SatName nvarchar(100),@SunName nvarchar(100) -- niezależnie od języka traktujemy sobotę i niedzielę jako dzień wolny
SELECT @SatName = DATENAME(dw,5), @SunName = DATENAME(dw,6)
SELECT @days = DATEDIFF(d,@StartDate ,@EndDate ) - DATEDIFF(wk,@StartDate ,@EndDate ) * 2
- CASE
WHEN DATENAME(dw, @StartDate) <> @SatName AND DATENAME(dw, @EndDate) = @SatName THEN 1
WHEN DATENAME(dw, @StartDate) = @SatName AND DATENAME(dw, @EndDate) <> @SatName THEN -1 ELSE 0
END
- (SELECT COUNT(*) FROM dbo.GetHolidays(year(@StartDate))
WHERE HolidayDate BETWEEN @StartDate AND @EndDate AND DATENAME(dw, HolidayDate) <> @SatName
AND DATENAME(dw, HolidayDate) <> @SunName) RETURN (@days)
END
GO
Gotowe. Jedyne ograniczenie tej funkcji to to, że obydwie daty muszą zawierać się w jednym roku. Oczywiście, w razie potrzeby można rozbudować tę funkcję.
Kompletny kod znajdziecie tutaj.