Web design and hosting, database, cloud and social media solutions that deliver business results
  • Des solutions d'affaires
    • Automatisation des processus robotiques
    • Logiciel
    • Services de base
      • Intégration de données
      • Power BI
      • Services d'entrepôt de données
      • Tools
    • Conception de site Web
      • Localisation et traduction Web
      • Sécurité du site Web
    • Services Cloud
      • Microsoft Azure
    • Média Social
    • Microsoft Office
  • Académie
    • Conception de bases de données d'apprentissage
      • Utilisation de données SQL Server
      • Obtenir la requête ouverte
      • Plan de maintenance SQL Server 2008
      • Utilisation des dates SQL Server
      • Utilisation de SQL Server Pivot-Unpivot
      • Utiliser SQL Server Fonctions
    • Apprentissage de la conception Web
      • CSS
      • ASP NET
    • Cloud d'apprentissage et services informatiques
      • Demande SSL et génération de fichier PFX dans OpenSSL étapes simples
  • Sur
    • Carrières
    • Équipe
      • Ali Al Amine
      • Chester Copperpot
      • Gavin Clayton
      • Suneel Kumar
    • Portefeuille
عربى (AR)čeština (CS)Deutsch (DE)English (EN-GB)English (EN-US)Español (ES)فارسی (FA)Français (FR)हिंदी (HI)italiano (IT)日本語 (JA)polski (PL)Português (PT)русский (RU)Türk (TR)中国的 (ZH)

Jours ouvrables entre les dates dans une fonction SQL Server avec des options flexibles

Une fonction SQL Server flexible et réutilisable qui calculera le nombre de jours entre deux dates avec la possibilité d'exclure les jours fériés ou les week-ends

Si vous avez vérifié les autres articles, vous avez peut-être déjà le tableau, sinon utilisez le code ci-dessous pour le créer. Ceux-ci sont basés sur des vacances standard en Angleterre et au Pays de Galles. Sur la page principale, il existe des fonctions pour d'autres pays.

CREATE TABLE Dates.Calendar(CalendarDate DATETIME2 NOT NULL CONSTRAINT PK_CalendarDate PRIMARY KEY,CalendarCA AS (DATEDIFF(DAY,DATEADD(DAY,1-DATEPART(DAY,CalendarDate),CalendarDate),CalendarDate)/7)+1 PERSISTED,CalendarCD AS (DATEDIFF(DAY,CalendarDate,DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,CalendarDate),CalendarDate))))/7)+1 PERSISTED,WeekDayID AS (DATEPART(weekday,[CalendarDate])),WeekDayName AS (case DATEPART(weekday,[CalendarDate]) when (1) then 'Sunday' when (2) then 'Monday' when (3) then 'Tuesday' when (4) then 'Wednesday' when (5) then 'Thursday' when (6) then 'Friday' when (7) then 'Saturday'  end))GODECLARE @D DATETIME2='1850-01-01'WHILE @D<='2099-12-31' BEGININSERT INTO Dates.Calendar(CalendarDate) SELECT @DSET @D=DATEADD(DAY,1,@D)ENDGOCREATE TABLE Dates.CalendarHolidays(CalendarDate DATETIME2 NOT NULL,CalendarFunction INT NOT NULL,HolidayType VARCHAR(100) NULL,CONSTRAINT PK_Holidays_Id PRIMARY KEY(CalendarDate,CalendarFunction))GO/*English & Welsh Holidays*/INSERT INTO Dates.CalendarHolidaysSELECT CalendarDate,0,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION  --New Years DaySELECT CalendarDate,0,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION --Good FridaySELECT CalendarDate,0,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION --Easter MondaySELECT CalendarDate,0,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION --May HolidaysSELECT CalendarDate,0,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCD=1) UNION --August HolidaysSELECT CalendarDate,0,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION  --Christmas DaySELECT CalendarDate,0,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26  --Boxing DayGO

Maintenant que nous avons les données, nous pouvons créer une fonction qui parcourt chaque jour du début à la fin et ajoute 1 en fonction des variables connectées comme ci-dessous:

  • @DateFrom - La date de début de votre calcul
  • @CalendarFunction - La fonction de type de vacances que vous souhaitez utiliser
  • @DateTo - La fin de la plage de dates que vous souhaitez utiliser
  • @AdjustMode - Calculez les jours entre les dates comme 0 exclusif ou 1 inclusif du premier jour.
  • @AdjustWeekend - Exclut les week-ends de vos calculs
  • @AdjustHolidays - Exclut les jours fériés si la fonction vacances correspond

CREATE FUNCTION Dates.GetDaysAdjusted(@DateFrom DATETIME2,@CalendarFunction INT,@DateTo AS DATETIME2,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS INT AS BEGIN/*@AdjustMode 0=Count whole days only,1=Any day counts as 1*/IF @DateFrom>@DateTo BEGINDECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFromSELECT @DateFrom=@T,@DateTo=@FENDDECLARE @Count AS INT=0,@DateAs DATETIME2=@DateFromWHILE @Date < @DateTo BEGINIF ((DATEPART(WEEKDAY,@Date)IN (1,7)AND @AdjustWeekEnds=1)OREXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunctionAND @AdjustHolidays=1))BEGINSELECT @Count = @Count + 1ENDSELECT @Date=DATEADD(DAY,1,@Date)ENDRETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustModeENDGOSELECT Dates.GetDaysAdjusted('2014-01-01',0,'2014-01-31',1,1,1)--22SELECT Dates.GetDaysAdjusted('2014-02-01',0,'2014-02-28',1,1,1)--20SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-02',1,1,1)--2SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-05',1,1,1)--2SELECT Dates.GetDaysAdjusted('2014-05-01',0,'2014-05-06',1,1,1)--3

Author

Was this helpful?

Please note, this commenting system is still in final testing.
Copyright Claytabase Ltd 2020

Registered in England and Wales 08985867

RSSLoginLink Politique de cookieSitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+442392064871info@claytabase.comClaytabase Ltd, Unit 3d, Rink Road Industrial Estate, PO33 2LT, United Kingdom
Les paramètres de ce site sont configurés pour autoriser tous les cookies. Ceux-ci peuvent être modifiés sur notre page cookies. En continuant à utiliser ce site, vous acceptez l'utilisation de cookies.
Ousia Logo
Logout
Ousia CMS Loader