Większość pracy, którą podjęliśmy wcześniej, dotyczyła pracy z datami, wiekami i różnymi innymi informacjami. Rzeczy takie jak obliczanie wieku nie są tak proste, jak mogłyby być. Napisaliśmy wcześniej kilka artykułów, ale teraz są one zastępowane przez kilka bardziej elastycznych funkcji, aby zmniejszyć liczbę potrzebnych funkcji.
W tym artykule przygotujemy naszą tabelę kalendarza do ponownego wykorzystania i świąteczną tabelę świąteczną, a następnie wypełnimy ją. Różne funkcje będą zapisywane jako oddzielne artykuły, gdzie można uzyskać dalsze informacje.
Mamy tendencję do korzystania z udostępnionej bazy danych "Użyteczność", w której znajdują się wszystkie nasze funkcje, aby zmniejszyć potrzebę kontroli zmian i spójne dane.
Jeśli masz wspólną bazę danych lub wiesz, gdzie chcesz ją przechowywać, te funkcje są darmowe, ale cały kod został zaprojektowany do uruchamiania w jednym skrypcie w SQL Server 2016.
Uwaga! - Może być łatwiejsze tworzenie bazy danych w SSMS
Wskazówka! - Ustaw opcje na proste, aby uniknąć rozrostu pliku logu, a dane są mniej prawdopodobne, aby być krytyczne dla biznesu.
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
Aby ułatwić zarządzanie, stworzyliśmy schemat o nazwie Daty i przechowujemy w nim wszystkie nasze powiązane informacje. Myśl o schemacie w tym sensie jako o pojemniku lub przestrzeni nazw dla powiązanych obiektów.
Wskazówka! - Podczas tworzenia schematu wsadowych instrukcji SQL, należy zawinąć go w instrukcję sp_executesql, aby uniknąć narzekania na to, że system jest jedyną instrukcją wsadową. Jeśli istnieje, umieść go w bloku try, aby umożliwić uruchomienie kodu.
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
Istnieje kilka wspólnych funkcji, które będą używane dalej, dlatego udostępnimy kod i łącze wraz z wyjaśnieniem na każdej stronie. Jest kilka rzeczy do zapamiętania:
Standardowe obliczenia roku DateDiff mają charakter czysto roczny, dlatego aby obliczyć wiek, należy sprawdzić, czy ta data jest wyższa, czy nie. Nasza funkcja GetLeapYear była najrozsądniejszym sposobem na wypracowanie, gdybyśmy musieli dodać dzień.
Data wielkanocna może się nie udać, jeśli nie używasz nowej funkcji DateFromParts z powodu braku dopełnienia dziennego.
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
Do planowania i powtarzania używamy własnej tabeli planowania. To pozwala nam obliczyć, jak często coś powinno się powtarzać.
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
Nasza tabela kalendarza jest skonfigurowana tak, aby działała przez dowolny okres w zakresie DATETIME2. W ogólnym użyciu poniższy kod daje zakres obejmujący większość scenariuszy.
CalendarCA i CalendarCD to numery tygodniowe rosnące i malejące dla każdego miesiąca i używane w odsyłaczu do naszej tabeli częstotliwości powyżej.
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
Zaprojektowaliśmy świąteczną tabelę tak, aby była jak najbardziej elastyczna i pozwalała na przechowywanie różnych opcji poprzez dodanie kolumny Funkcja kalendarza. Poniżej przechowujemy święta dla Anglii i Walii, Szkocji i Irlandii Północnej osobno, odnosząc się do już utworzonej tabeli kalendarzy.
Teoretycznie możesz mieć tyle schematów, ile chcesz.
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
Są trzy funkcje, które łączą je na różne sposoby, wszystkie z bardzo podobnymi opcjami.
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
Chociaż możesz dodać sprzężenie do swojej instrukcji select, łatwiej nam było odwoływać się do innej funkcji. Dodając dwie daty, częstotliwość oraz zarówno rosnący, jak i malejący numer miesięcznego tygodnia, odpowiednie daty mogą być sprawdzane pod kątem dopasowania.
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
Możemy teraz przetestować te dane, generując fałszywą tabelę planowania.
W naszym przykładzie tworzenia raportu, który odbędzie się w dniu 1 stycznia 2017 r., I powtarzać co miesiąc w pierwszym z każdym możliwym działaniem daty dostępne.
Patrząc na 01.01.2018 w odosobnieniu, weekendy i święta mogą zmienić zakres dat na dowolny od 29.12.2017 do 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