Daty - obliczanie dni roboczych między dwiema datami wyłączając wakacje w programie SQL Server

Utwórz funkcję, która obliczy liczbę dni roboczych (z wyjątkiem dni świątecznych) między dwiema datami.

Stwórz tabelę świąteczną

Jeśli wyewidencjonowano inne artykuły, możesz mieć już stół, jeśli nie, a następnie użyj poniższego kodu, aby go utworzyć. Są one oparte na standardowych wakacjach w Wielkiej Brytanii.

SQL

CREATE TABLE Holidays(

[Date] DATE CONSTRAINT PK_Holidays_Id PRIMARY KEY,

HolidayType VARCHAR(100) NULL)

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-01-01 00:00:00','New Years Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-03-29 00:00:00','Good Friday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-04-01 00:00:00','Easter Monday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-05-06 00:00:00','Early May Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-05-27 00:00:00','Spring Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-08-26 00:00:00','Summer Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-12-25 00:00:00','XMAS Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2013-12-26 00:00:00','Boxing Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2014-01-01 00:00:00','New Years Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2014-04-18 00:00:00','Good Friday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2014-04-21 00:00:00','Easter Monday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2014-05-05 00:00:00','Early May Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2014-05-26 00:00:00','Spring Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2014-08-25 00:00:00','Summer Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2014-12-25 00:00:00','XMAS Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2014-12-26 00:00:00','Boxing Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2015-01-01 00:00:00','New Years Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2015-04-03 00:00:00','Good Friday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2015-04-06 00:00:00','Easter Monday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2015-05-04 00:00:00','Early May Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2015-05-25 00:00:00','Spring Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2015-08-31 00:00:00','Summer Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2015-12-25 00:00:00','XMAS Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2015-12-28 00:00:00','Boxing Day Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2016-01-01 00:00:00','New Years Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2016-03-25 00:00:00','Good Friday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2016-03-28 00:00:00','Easter Monday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2016-05-02 00:00:00','Early May Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2016-05-30 00:00:00','Spring Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2016-08-29 00:00:00','Summer Bank Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2016-12-26 00:00:00','Baxing Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2016-12-27 00:00:00','XMAS Day Holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2017-01-02 00:00:00','New Year’s Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2017-04-14 00:00:00','Good Friday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2017-04-17 00:00:00','Easter Monday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2017-05-01 00:00:00','Early May bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2017-05-29 00:00:00','Spring bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2017-08-28 00:00:00','Summer bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2017-12-25 00:00:00','Christmas Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2017-12-26 00:00:00','Boxing Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2018-01-01 00:00:00','New Year’s Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2018-03-30 00:00:00','Good Friday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2018-04-02 00:00:00','Easter Monday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2018-05-07 00:00:00','Early May bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2018-05-28 00:00:00','Spring bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2018-08-27 00:00:00','Summer bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2018-12-25 00:00:00','Christmas Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2018-12-26 00:00:00','Boxing Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2019-01-01 00:00:00','New Year’s Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2019-04-19 00:00:00','Good Friday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2019-04-22 00:00:00','Easter Monday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2019-05-06 00:00:00','Early May bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2019-05-27 00:00:00','Spring bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2019-08-26 00:00:00','Summer bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2019-12-25 00:00:00','Christmas Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2019-12-26 00:00:00','Boxing Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2020-01-01 00:00:00','New Year’s Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2020-04-10 00:00:00','Easter Monday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2020-04-13 00:00:00','Early May bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2020-05-04 00:00:00','Spring bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2020-05-25 00:00:00','Summer bank holiday'

INSERT INTO Holidays([Date],HolidayType) SELECT '2020-12-25 00:00:00','Christmas Day'

INSERT INTO Holidays([Date],HolidayType) SELECT '2020-12-28 00:00:00','Boxing Day'

Utwórz funkcję

Teraz mamy dane, możemy utworzyć funkcję, która przechodzi przez każdy dzień od początku do daty zakończenia i dodaje 1 do @Count, gdzie nie jest sobota, niedziela lub w bazie danych dni świątecznych.

SQL

CREATE FUNCTION [dbo].[CalcWorkDaysBetween](@StartDate As DateTime,@EndDate AS DateTime)

RETURNS INT AS BEGIN

SET @EndDate =DATEADD(DAY,1,@EndDate)

DECLARE @Count AS Int= 0

DECLARE @Date As DATE=@StartDate

WHILE @Date < @EndDate

BEGIN

IF (DATEPART(WEEKDAY,@Date)IN(1,7) OR (SELECT Count(*) FROM Holidays WHERE Date=@Date)=1)

BEGIN

SELECT @Count = @Count + 1

END

SELECT @Date=DATEADD(DAY, 1,@Date)

END

RETURN DATEDIFF(DAY,@StartDate,@EndDate)- @Count

END

SELECT dbo.CalcWorkDaysBetween('2014-01-01','2014-01-31') --22

SELECT dbo.CalcWorkDaysBetween('2014-02-01','2014-02-28') --20

SELECT dbo.CalcWorkDaysBetween('2014-05-01','2014-05-02') --2

SELECT dbo.CalcWorkDaysBetween('2014-05-01','2014-05-05') --2

SELECT dbo.CalcWorkDaysBetween('2014-05-01','2014-05-06') --3