La majorité du travail que nous avons déjà entrepris a impliqué de travailler avec des dates, des âges et d'autres informations. Des choses comme le calcul de l'âge ne sont pas aussi simples qu'elles pourraient l'être. Nous avons déjà écrit une sélection d'articles, mais ceux-ci sont maintenant remplacés par quelques fonctions plus flexibles pour réduire le nombre de fonctions dont vous avez besoin.
Dans cet article, nous allons configurer notre table de calendrier et notre table des jours fériés réutilisables, puis les remplir. Diverses fonctions seront écrites en tant qu'articles distincts où vous pourrez ensuite obtenir plus d'informations.
Nous avons tendance à utiliser une base de données «Utilitaire» partagée avec toutes nos fonctions pour réduire les besoins de contrôle des changements et les données cohérentes.
Si vous avez une base de données partagée, ou savez où vous voulez stocker ces fonctions, alors n'hésitez pas, cependant tout le code d'ici a été conçu pour s'exécuter dans un seul script dans SQL Server 2016.
Remarque! - Il peut être plus facile de créer votre base de données dans SSMS
Pointe! - Définissez les options sur simple afin d'éviter le gonflement du fichier journal et les données sont moins susceptibles d'être critiques pour l'entreprise.
CREATE DATABASE [Utilities] CONTAINMENT = NONE ONPRIMARY ( NAME = N'Utilities',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER13\MSSQL\DATA\Utilities.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB) LOG ON ( NAME = N'Utilities_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER13\MSSQL\DATA\Utilities_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB)GO
Pour simplifier la gestion, nous avons créé un schéma appelé Dates et stocké toutes les informations associées. Pensez au schéma en ce sens comme un conteneur ou un espace de noms pour des objets connexes.
Pointe! - Lors de la création d'un schéma dans les instructions SQL par lots, placez-le dans une instruction sp_executesql pour éviter que le système ne se plaint que ce soit la seule instruction du lot. Si tel est le cas, enveloppez-le dans un bloc try pour permettre au code de s'exécuter.
USE UtilitiesGOSET NOCOUNT ON;BEGIN TRYEXEC sp_executesql N'CREATE SCHEMA Dates'END TRY BEGIN CATCH END CATCH;GOBEGIN TRYEXEC sp_executesql N'CREATE SCHEMA Test'END TRY BEGIN CATCH END CATCH;GO
Il y a quelques fonctions partagées qui seront utilisées plus loin, donc nous allons fournir le code et le lien, avec une explication sur chaque page. Il y a un certain nombre de choses à noter:
Les calculs d'année standard de DateDiff sont purement annuels, donc pour calculer l'âge, vous devez vérifier si cette date est supérieure ou non. Notre fonction GetLeapYear était le moyen le plus raisonnable de travailler si nous avions besoin d'ajouter un jour.
La date de Pâques peut échouer si vous n'utilisez pas la nouvelle fonction DateFromParts, en raison du manque de remplissage du jour.
CREATE FUNCTION Dates.GetLeapYear(@Date DATETIME2) RETURNS BIT AS BEGINDECLARE @Ret BIT=(CASE WHEN DATEPART(YEAR,@Date)%4<>0 OR (DATEPART(YEAR,@Date)%100=0 AND DATEPART(YEAR,@Date)%400<>0) THEN 0 ELSE 1 END)RETURN @RetENDGO
CREATE FUNCTION Dates.GetAge(@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGINIF @Until IS NULL SET @Until=CONVERT(DATE,GETDATE())DECLARE @Age INT=DATEDIFF(YEAR,@Date,@Until)+(CASE WHEN DATEPART(DAYOFYEAR,@Date)>(DATEPART(DAYOFYEAR,@Until)+(CASE WHEN dbo.GetLeapYear(@Date)=1 AND DATEPART(DAYOFYEAR,@Date)>59 THEN 1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDGO
CREATE FUNCTION Dates.DatePad(@PadValue NVARCHAR(100),@PadLen INT) RETURNS NVARCHAR(4) AS BEGINRETURN ISNULL(REPLICATE('0',@PadLen-LEN(@PadValue))+@PadValue,LEFT(@PadValue,@PadLen))ENDGO
CREATE FUNCTION Dates.GetEasterDate(@Year INT) RETURNS DATETIME2 AS BEGIN/*Calculate date of easter based on Year passed - adjusted from Wikipedia*/Declare @GregorianEaster DATETIME2Declare @a INT, @b INT, @c INT, @d INT, @e INT, @f INT, @g INT, @h INT, @i INT, @k INT, @L INT, @m INT, @Month INT, @Day INTSET @a = @Year % 19SET @b = floor(@Year/ 100)SET @c = @Year % 100SET @d = floor(@b/ 4) SET @e = @b % 4 SET @f = floor((@b+ 8) / 25) SET @g = floor((@b- @f + 1)/3) SET @h = (19*@a+ @b - @d - @g + 15) % 30 SET @i = floor(@c/ 4) SET @k = @c % 4 SET @L = (32 + 2*@e + 2*@i- @h - @k) % 7 SET @m = floor((@a+ 11*@h + 22*@L)/ 451) SET @month = floor((@h+ @L - 7*@m + 114) / 31) SET @day = (@h + @L - 7*@m + 114) % 31 + 1 SET @GregorianEaster = Dates.DateFromParts(@Year,@Month,@Day) RETURN @GregorianEaster END GO
Pour la planification et la récurrence, nous utilisons notre propre table de planification. Cela nous permet de calculer à quelle fréquence quelque chose doit être répété.
CREATE TABLE Dates.CalendarFrequency(FrequencyID NVARCHAR(2),FrequencyName NVARCHAR(100),FrequencyOrder INT) GO INSERT INTO Dates.CalendarFrequency SELECT '','Once',0 UNION SELECT '7','Weekly',1 UNION SELECT '14','Fortnightly',2 UNION SELECT '32','Monthly (Same Day 1st,15th,28th etc)',3 UNION SELECT '1','Monthly (First Monday,Wednesday etc)',4 UNION SELECT '2','Monthly (Second Monday,Wednesday etc)',5 UNION SELECT '3','Monthly (Third Monday,Wednesday etc)',6 UNION SELECT '4','Monthly (Fourth Monday,Wednesday etc)',10 UNION SELECT '-1','Monthly (Last Monday,Wednesday etc)',7 UNION SELECT '-2','Monthly (Second Last Monday,Wednesday etc)',8 UNION SELECT '-3','Monthly (Third Last Monday,Wednesday etc)',9 UNION SELECT '-4','Monthly (Fourth Last Monday,Wednesday etc)',11 ORDER BY 3 GO
Notre table de calendrier est configurée pour fonctionner pendant toute la durée de la plage DATETIME2. En général, le code ci-dessous vous donnera une plage couvrant la plupart des scénarios.
CalendarCA et CalendarCD sont les numéros de semaine ascendants et descendants pour chaque mois, et utilisés dans une référence croisée avec notre tableau de fréquence ci-dessus.
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)) GO DECLARE @D DATETIME2='1850-01-01' WHILE @D<='2099-12-31' BEGIN INSERT INTO Dates.Calendar(CalendarDate) SELECT @D SET @D=DATEADD(DAY,1,@D) END GO
Nous avons conçu la table des jours fériés de manière à ce qu'elle soit la plus flexible possible et à permettre le stockage de différentes options en ajoutant la colonne Fonction calendrier. Ci-dessous nous avons stocké les vacances pour l'Angleterre et le Pays de Galles, l'Ecosse et l'Irlande du Nord séparément, croisant le tableau de calendrier déjà créé.
En théorie, vous pouvez avoir autant de régimes que vous le souhaitez.
CREATE 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.CalendarHolidays SELECT CalendarDate,0,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION --New Years Day SELECT CalendarDate,0,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Good Friday SELECT CalendarDate,0,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Easter Monday SELECT CalendarDate,0,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May Holidays SELECT CalendarDate,0,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCD=1) UNION--August Holidays SELECT CalendarDate,0,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas Day SELECT CalendarDate,0,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing Day GO /*Scotish Holidays*/ INSERT INTO Dates.CalendarHolidays SELECT CalendarDate,1,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1AND DATEPART(DAY,CalendarDate)=1 UNION --New Years Day SELECT CalendarDate,1,'2nd' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=2 UNION --New Years Day SELECT CalendarDate,1,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate)))UNION--Good Friday SELECT CalendarDate,1,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May Holidays SELECT CalendarDate,1,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCA=1) UNION--August Holidays SELECT CalendarDate,1,'St Andrews' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=11 AND DATEPART(DAY,CalendarDate)=30 UNION --St Andrews SELECT CalendarDate,1,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas Day SELECT CalendarDate,1,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing Day GO /*Northern Irish Holidays*/ INSERT INTO Dates.CalendarHolidays SELECT CalendarDate,2,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION --New Years Day SELECT CalendarDate,2,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Good Friday SELECT CalendarDate,2,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Easter Monday SELECT CalendarDate,2,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May Holidays SELECT CalendarDate,2,'Battle of the Boyne' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=7 AND DATEPART(DAY,CalendarDate)=12 UNION --Battle of the Boyne SELECT CalendarDate,2,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCA=1) UNION--August Holidays SELECT CalendarDate,2,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas Day SELECT CalendarDate,2,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing DayGO
Il y a trois fonctions utilisées qui les combinent de diverses manières, toutes avec des options très similaires.
CREATE FUNCTION Dates.GetDateAdjusted(@AdjustDate AS DATETIME2,@CalendarFunction INT,@AdjustDays AS INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS DATETIME2 AS BEGIN SELECT @AdjustDate=DATEADD(DAY,(CASE @AdjustMode WHEN 0 THEN -1 ELSE 1 END),@AdjustDate), @AdjustDays=(CASE @AdjustMode WHEN 0 THEN @AdjustDays+1 ELSE (0-@AdjustDays)-1 END) DECLARE @AdjustCount INT=0,@AdjustWorkDays INT=0,@Date DATETIME2=@AdjustDate /*Forwards*/ WHILE @AdjustMode=0 AND @AdjustWorkDays < @AdjustDays BEGIN SET @AdjustCount=@AdjustCount+1 SET @Date=DATEADD(DAY,@AdjustCount,@AdjustDate) IF NOT ((DATEPART(WEEKDAY,@Date) IN (1,7) AND @AdjustWeekEnds=1) OR EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunction AND @AdjustHolidays=1)) BEGIN SET @AdjustWorkDays = @AdjustWorkDays + 1 END END /*Backwards*/ WHILE @AdjustMode=1 AND @AdjustWorkDays > @AdjustDays BEGIN SET @AdjustCount=@AdjustCount-1 SET @Date=DATEADD(DAY,@AdjustCount,@AdjustDate) IF NOT ((DATEPART(WEEKDAY,@Date) IN (1,7)AND @AdjustWeekEnds=1) OR EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunction AND @AdjustHolidays=1)) BEGIN SET @AdjustWorkDays = @AdjustWorkDays - 1 END END RETURN @Date END GO
CREATE FUNCTION Dates.GetDaysAdjusted(@DateFrom As 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 BEGIN DECLARE @T DATETIME2=@DateTo,@F DATETIME2=@DateFrom SELECT @DateFrom=@T,@DateTo=@F END DECLARE @Count AS INT=0,@Date As DATETIME2=@DateFrom WHILE @Date < @DateTo BEGIN IF ((DATEPART(WEEKDAY,@Date) IN (1,7) AND @AdjustWeekEnds=1) OR EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunction AND @AdjustHolidays=1)) BEGIN SELECT @Count = @Count + 1 END SELECT @Date=DATEADD(DAY,1,@Date) END RETURN (DATEDIFF(DAY,@DateFrom,@DateTo)-(@Count))+@AdjustMode END GO
CREATE FUNCTION Dates.GetMonthAdjusted(@Month As DATETIME2,@CalendarFunction INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS INT AS BEGIN DECLARE @StartDate DATETIME2=CONVERT(DATE,DATEADD(DAY,1-DAY(@Month),@Month)) DECLARE @EndDate DATETIME2=DATEADD(DAY,-1,DATEADD(MONTH,1,@StartDate)),@Count AS INT=0,@Date As DATETIME2=@StartDate WHILE @Date < @EndDate BEGIN IF ((DATEPART(WEEKDAY,@Date) IN(1,7) AND @AdjustWeekEnds=1) OR EXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunction AND @AdjustHolidays=1)) BEGIN SELECT @Count = @Count + 1 END SET @Date=DATEADD(DAY, 1,@Date) END RETURN (DATEDIFF(DAY,@StartDate,@EndDate)-(@Count))+@AdjustMode END
Bien que vous puissiez ajouter une jointure dans votre instruction select, nous avons trouvé plus facile de faire référence dans une autre fonction. En ajoutant deux dates, la fréquence et les numéros de semaine mensuels ascendant et descendant, les dates pertinentes peuvent être vérifiées pour une correspondance.
CREATE FUNCTION Dates.DateJoin(@CalendarDate DATETIME,@CrossoverDate DATETIME,@Frequency INT,@WeekAsc INT,@WeekDesc INT) RETURNS BIT AS BEGIN DECLARE @J BIT=0 /*Once*/ IF @CrossoverDate=@CalendarDate SET @J=1 /*Weekly/Fornightly*/ IF @J=0 AND @Frequency IN (7,14) BEGIN IF DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND DATEDIFF(DAY,@CrossoverDate,@CalendarDate)%@Frequency=0 SET @J=1 END /*Monthly*/ IF @J=0 AND @Frequency IN (32) BEGIN IF (DATEPART(DAY,@CalendarDate)=DATEPART(DAY,@CrossoverDate) ) OR ( DATEPART(MONTH,DATEADD(DAY,1,@CalendarDate))<> DATEPART(MONTH,@CalendarDate) AND DATEPART(MONTH,DATEADD(DAY,1,DATEADD(MONTH,DATEDIFF(MONTH,@CrossoverDate,@CalendarDate),@CrossoverDate)))<> DATEPART(MONTH,DATEADD(MONTH,DATEDIFF(MONTH,@CrossoverDate,@CalendarDate),@CrossoverDate))) SET @J=1 END /*First/Second/Third/Fourth*/ IF @J=0 AND @Frequency IN (1,2,3,4)IF (DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND @WeekAsc=@Frequency) SET @J=1 /*First/Second/Third/Fourth Last*/ IF @J=0 AND @Frequency IN (-1,-2,-3,-4)IF (DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND @WeekDesc=(-@Frequency)) SET @J=1 RETURN @J END GO
Nous pouvons maintenant tester ces données en générant une fausse table de planification.
Dans notre exemple, créez un rapport pour le 1er janvier 2017, et répétez chaque mois le premier avec toutes les dates disponibles.
En regardant le 01/01/2018 en isolation, le comportement du week-end et des jours fériés pourrait changer la plage de dates du 29/12/2017 au 02/01/2018.
CREATE TABLE Test.Schedule( ReportID INT, ReportName NVARCHAR(100), ReportStartDate DATETIME2, ReportFrequency INT, ReportAdjustBehaviour BIT, ReportAdjustWeekend BIT, ReportAdjustHoliday BIT, ProducedBy NVARCHAR(100)) GO INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=0,Holidays=0','2017-01-01',32,1,0,0,'' INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=1,Holidays=0','2017-01-01',32,1,1,0,'' INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=0,Holidays=1','2017-01-01',32,1,0,1,'' INSERT INTO Test.Schedule SELECT 1,'Schedule Before - Weekends=1,Holidays=1','2017-01-01',32,1,1,1,'' INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=0,Holidays=0','2017-01-01',32,0,0,0,'' INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=1,Holidays=0','2017-01-01',32,0,1,0,'' INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=0,Holidays=1','2017-01-01',32,0,0,1,'' INSERT INTO Test.Schedule SELECT 1,'Schedule After - Weekends=1,Holidays=1','2017-01-01',32,0,1,1,'' GO SELECT (case DATEPART(weekday,DateAdjusted) 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) DayAdjusted, DateAdjusted,WeekDayName,CalendarDate,ReportName,FrequencyName FROM ( SELECT Dates.GetDateAdjusted(CalendarDate,0,0,ReportAdjustBehaviour,ReportAdjustWeekend,ReportAdjustHoliday) DateAdjusted,c.CalendarDate,WeekDayName,s.*,f.FrequencyName FROM (SELECT * FROM Dates.Calendar WHERE CalendarDate BETWEEN '2018-01-01' AND '2018-12-31') c INNER JOIN Test.Schedule s ON (CalendarDate>=ReportStartDate --AND CalendarDate>GETDATE() ) AND (Dates.DateJoin(CalendarDate,ReportStartDate,ReportFrequency,CalendarCA,CalendarCD)=1) INNER JOIN Dates.CalendarFrequency f ON f.FrequencyID=ReportFrequency ) x ORDER BY CalendarDate,DateAdjusted,ReportName