Verwenden von SQL Server Datum

Die Verwendung von Daten in SQL Server kann frustrierend und zeitraubend sein. Er teilt uns einige unserer portabelsten Funktionen mit einer Kalender-Tabelle, um Feiertage und Wochenenden zu speichern und zu berechnen sowie wiederkehrende Häufigkeiten festzulegen.

Über

Der Großteil der Arbeit, die wir bisher geleistet haben, umfasste die Arbeit mit Daten, Alter und verschiedenen anderen Informationen. Dinge wie das Berechnen des Alters sind nicht so einfach wie sie sein könnten. Wir haben bereits eine Auswahl von Artikeln geschrieben, die jetzt jedoch durch einige flexiblere Funktionen ersetzt werden, um die Anzahl der benötigten Funktionen zu reduzieren.

In diesem Artikel werden wir unsere wiederverwendbare Kalender- und Feiertagstabelle einrichten und sie dann füllen. Verschiedene Funktionen werden als separate Artikel geschrieben, in denen Sie weitere Informationen erhalten.

Wir neigen dazu, eine gemeinsam genutzte "Utility" -Datenbank mit all unseren Funktionen zu verwenden, um Änderungsbedarf und konsistente Daten zu reduzieren.

Datenbank erstellen

Wenn Sie eine gemeinsam genutzte Datenbank haben oder wissen, wo Sie diese speichern möchten, sind diese Funktionen frei, jedoch wurde der gesamte Code von hier in SQL Server 2016 in einem einzigen Skript ausgeführt.

Hinweis! - Es kann einfacher sein, Ihre Datenbank in SSMS zu erstellen

Spitze! - Setzen Sie die Optionen auf "Einfach", um das Aufblähen der Protokolldatei zu vermeiden, und die Daten sind weniger wahrscheinlich geschäftskritisch.

SQL

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

Erstellen Sie ein Schema

Um die Verwaltung zu vereinfachen, haben wir ein Schema namens Dates erstellt und speichern alle damit verbundenen Informationen. Stellen Sie sich das Schema in diesem Sinne als Container oder Namespace für verwandte Objekte vor.

Spitze! - Wenn Sie ein Schema in Stapel-SQL-Anweisungen erstellen, müssen Sie es in eine sp_executesql-Anweisung einfügen, um zu vermeiden, dass das System darüber klagt, dass es die einzige Anweisung im Stapel ist. Wenn es existiert, dann wickle es in einen try-Block, damit der Code durchlaufen kann.

SQL

USE Utilities
GO
SET NOCOUNT ON;
BEGIN TRY
EXEC sp_executesql N'CREATE SCHEMA Dates'
END TRY BEGIN CATCH END CATCH;
GO
BEGIN TRY
EXEC sp_executesql N'CREATE SCHEMA Test'
END TRY BEGIN CATCH END CATCH;
GO

Gemeinsame Funktionen

Es gibt ein paar gemeinsame Funktionen, die weiter verwendet werden. Daher werden wir den Code und den Link mit einer Erklärung auf jeder Seite bereitstellen. Es gibt ein paar Dinge zu beachten:

Die Standard-DateDiff-Jahresberechnungen sind reine Jahreszahlen. Um das Alter zu berechnen, müssen Sie überprüfen, ob dieses Datum höher ist oder nicht. Unsere GetLeapYear-Funktion war der sinnvollste Weg, um herauszufinden, ob wir einen Tag hinzufügen mussten.

Das Osterdatum kann fehlschlagen, wenn Sie die neue DateFromParts-Funktion nicht verwenden, da der Tag nicht gefüllt ist.

GetLeapYear

CREATE FUNCTION Dates.GetLeapYear(@Date DATETIME2) RETURNS BIT AS BEGIN
DECLARE @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 @Ret
END
GO

mehr: Holen Sie sich Schaltjahr

GetAge

CREATE FUNCTION Dates.GetAge(@Date DATETIME2,@Until DATETIME2) RETURNS INT AS BEGIN
IF @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 @Age
END
GO

mehr: Holen Sie sich Alter

DatePad

CREATE FUNCTION Dates.DatePad(@PadValue NVARCHAR(100),@PadLen INT) RETURNS NVARCHAR(4) AS BEGIN
RETURN ISNULL(REPLICATE('0',@PadLen-LEN(@PadValue))+@PadValue,LEFT(@PadValue,@PadLen))
END
GO

mehr: Datum Auffüllfunktion

DateFromParts

CREATE FUNCTION Dates.DateFromParts(@Year SMALLINT,@Month SMALLINT,@Day SMALLINT) RETURNS DATETIME2 AS BEGIN
DECLARE @Date DATETIME2=Dates.DatePad(@Year,4)+'-'+Dates.DatePad(@Month,2)+'-'+Dates.DatePad(@Day,2)
RETURN @Date
END
GO

mehr: SQL Server-Funktion DATEFROMPARTS für Versionen vor 2012

GetEasterDate

CREATE FUNCTION Dates.GetEasterDate(@Year INT) RETURNS DATETIME2 AS BEGIN
/*Calculate date of easter based on Year passed - adjusted from Wikipedia*/
Declare @GregorianEaster DATETIME2
Declare @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 INT
SET @a = @Year % 19
SET @b = floor(@Year/ 100)
SET @c = @Year % 100
SET @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

Häufigkeitstabelle

Für die Terminplanung und Wiederholung verwenden wir unsere eigene Terminplanungstabelle. So können wir berechnen, wie oft etwas wiederholt werden sollte.

Calendar Frequency

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

Kalender Tabelle

Unsere Kalendertabelle ist so eingestellt, dass sie für einen beliebigen Zeitraum innerhalb des DATETIME2-Bereichs läuft. Im Allgemeinen wird der unten stehende Code Ihnen einen Bereich geben, der die meisten Szenarien abdecken würde.

CalendarCA und CalendarCD sind die Wochennummern, die für jeden Monat auf- und absteigen und in einem Querverweis mit unserer obigen Häufigkeitstabelle verwendet werden.

Create Calendar Table

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

Feiertage Tabelle

Wir haben die Feiertagstabelle so flexibel wie möglich gestaltet und ermöglichen das Speichern verschiedener Optionen durch Hinzufügen der Spalte Kalenderfunktion. Im Folgenden haben wir die Feiertage für England & Wales, Schottland und Nordirland separat gespeichert, indem wir die bereits erstellte Kalender Tabelle referenzieren.

In der Theorie können Sie so viele Schemata haben, wie Sie möchten.

Create Holidays Table


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 Day
GO

Clevere Bits

Es gibt drei Funktionen, die sie auf verschiedene Arten kombinieren, alle mit sehr ähnlichen Optionen.

GetDateAdjusted verschiebt das Datum abhängig von der Kalenderfunktion um eine bestimmte Anzahl von Tagen vor oder zurück, und ob Sie Feiertage und / oder Wochenenden einbeziehen möchten.
GetDaysAdjusted zählt die Anzahl der Tage zwischen zwei Daten, abhängig von der Kalenderfunktion, und ob Sie Feiertage und / oder Wochenenden einbeziehen möchten. Termine können inklusive oder exklusiv gezählt werden.
GetMonthAdjusted zählt die Anzahl der Tage in einem Monat, abhängig von der Kalenderfunktion, und ob Sie Feiertage und / oder Wochenenden einbeziehen möchten. Termine können inklusive oder exklusiv gezählt werden.

GetDateAdjusted

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

mehr: Datumsangaben - Hinzufügen von Arbeitstagen zu einem Datum ohne Feiertage

GetDaysAdjusted

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

mehr: Berechnung der Arbeitstage zwischen zwei Daten, ausgenommen Feiertage in SQL Server

GetMonthAdjusted

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

mehr: Dates - Berechnung der Arbeitstage in einem Monat ohne Feiertage

Die Daten zusammenführen

Sie können zwar einen Join zu Ihrer SELECT-Anweisung hinzufügen, wir haben es jedoch leichter gefunden, in einer anderen Funktion zu referenzieren. Durch Hinzufügen von zwei Daten, der Häufigkeit und der aufsteigenden und absteigenden monatlichen Wochennummer können die relevanten Daten für eine Übereinstimmung überprüft werden.

DateJoin Function


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

Testdatum

Wir können diese Daten jetzt testen, indem wir eine falsche Planungstabelle generieren.

In unserem Beispiel erstellen Sie einen Bericht, der am 1. Januar 2017 erscheint, und wiederholen Sie jeden Monat auf dem ersten mit jeder möglichen Datumsaktion.

Betrachtet man den 01.01.2018 isoliert, könnte das Wochenend- und Feiertagsverhalten den Zeitraum vom 29.12.2017 bis zum 02.01.2018 verändern.

Test Data Script


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

Results

DayAdjusted DateAdjusted WeekDayName CalenderDate ReportName FrequencyName Friday 29/12/2017 Monday 01/01/2018 Schedule Before - Weekends=1,Holidays=1 Monthly (Same Day 1st,15th,28th etc) Sunday 31/12/2017 Monday 01/01/2018 Schedule Before - Weekends=0,Holidays=1 Monthly (Same Day 1st,15th,28th etc) Monday 01/01/2018 Monday 01/01/2018 Schedule After - Weekends=0,Holidays=0 Monthly (Same Day 1st,15th,28th etc) Monday 01/01/2018 Monday 01/01/2018 Schedule After - Weekends=1,Holidays=0 Monthly (Same Day 1st,15th,28th etc) Monday 01/01/2018 Monday 01/01/2018 Schedule Before - Weekends=0,Holidays=0 Monthly (Same Day 1st,15th,28th etc) Monday 01/01/2018 Monday 01/01/2018 Schedule Before - Weekends=1,Holidays=0 Monthly (Same Day 1st,15th,28th etc) Tuesday 02/01/2018 Monday 01/01/2018 Schedule After - Weekends=0,Holidays=1 Monthly (Same Day 1st,15th,28th etc) Tuesday 02/01/2018 Monday 01/01/2018 Schedule After - Weekends=1,Holidays=1 Monthly (Same Day 1st,15th,28th etc)

DayAdjusted	DateAdjusted	WeekDayName	CalenderDate	ReportName	FrequencyName
Friday	29/12/2017	Monday	01/01/2018	Schedule Before - Weekends=1,Holidays=1	Monthly (Same Day 1st,15th,28th etc)
Sunday	31/12/2017	Monday	01/01/2018	Schedule Before - Weekends=0,Holidays=1	Monthly (Same Day 1st,15th,28th etc)
Monday	01/01/2018	Monday	01/01/2018	Schedule After - Weekends=0,Holidays=0	Monthly (Same Day 1st,15th,28th etc)
Monday	01/01/2018	Monday	01/01/2018	Schedule After - Weekends=1,Holidays=0	Monthly (Same Day 1st,15th,28th etc)
Monday	01/01/2018	Monday	01/01/2018	Schedule Before - Weekends=0,Holidays=0	Monthly (Same Day 1st,15th,28th etc)
Monday	01/01/2018	Monday	01/01/2018	Schedule Before - Weekends=1,Holidays=0	Monthly (Same Day 1st,15th,28th etc)
Tuesday	02/01/2018	Monday	01/01/2018	Schedule After - Weekends=0,Holidays=1	Monthly (Same Day 1st,15th,28th etc)
Tuesday	02/01/2018	Monday	01/01/2018	Schedule After - Weekends=1,Holidays=1	Monthly (Same Day 1st,15th,28th etc)