Dates - Calcul des jours ouvrables dans un mois hors vacances

Créez une fonction SQL qui calculera le nombre de jours ouvrables (hors jours fériés) dans un mois dans SQL Server 2008

Créer une table de vacances

Si vous avez vérifié les autres articles, vous pouvez déjà avoir le tableau, sinon utilisez le code ci-dessous pour le créer. Ceux-ci sont basés sur des vacances standard au Royaume-Uni.

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'

Créer une fonction

Maintenant, nous avons les données, nous pouvons créer une fonction qui boucle chaque jour du début à la fin et ajoute 1 à @Count où ce n'est pas un samedi, dimanche ou dans votre base de données de jours de vacances (qui sont différents par pays) .

SQL

CREATE FUNCTION [dbo].[CalcWorkDaysMonth](@Month As DateTime)

RETURNS INT AS BEGIN

DECLARE @StartDate DATE=CONVERT(DATE,DATEADD(DAY,1-DAY(@Month),@Month))

DECLARE @EndDate DATE=DATEADD(MONTH,1,@StartDate)

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.CalcWorkDaysMonth('2014-01-01') --22

SELECT dbo.CalcWorkDaysMonth('2014-02-04') --20

SELECT dbo.CalcWorkDaysMonth('2014-05-15') --20 (22-2)

Author

Gavin Clayton

J'ai formé Claytabase en 2010 comme moyen de poursuivre mon travail avec SQL Server et ASP.NET. Cela a abouti au fait que le système de gestion de contenu d'Ousia est passé du concept à l'un des CMS les plus rapides sur le marché.

Google Translate