Używanie dat serwera SQL

Używanie dat w SQL Server może być frustrujące i czasochłonne. Dzieli niektóre z naszych najbardziej przenośnych funkcji za pomocą tabeli kalendarza do przechowywania i obliczania świąt i weekendów, a także ustaw częstotliwość powtarzania.

O

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.

Utwórz bazę danych

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.

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

Utwórz schemat

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.

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

Wspólne funkcje

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.

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

dalszy: Zdobądź rok przestępny

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

dalszy: Zdobądź wiek

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

dalszy: Funkcja wypełniania dat

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

dalszy: Funkcja SQL Server DATEFROMPARTS dla wersji pre 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

Tabela częstotliwości

Do planowania i powtarzania używamy własnej tabeli planowania. To pozwala nam obliczyć, jak często coś powinno się powtarzać.

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

Tabela kalendarza

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

Tabela świąt

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

Sprytne bity

Są trzy funkcje, które łączą je na różne sposoby, wszystkie z bardzo podobnymi opcjami.

GetDateAdjusted przenosi datę do przodu lub wstecz o określoną liczbę dni, w zależności od funkcji kalendarza i tego, czy chcesz uwzględnić dni świąteczne, czy weekendy.
GetDaysAdjusted liczy liczbę dni między dwiema datami, w zależności od funkcji kalendarza i tego, czy chcesz uwzględnić dni świąteczne, czy weekendy. Daty mogą być liczone łącznie lub wyłącznie.
Funkcja GetMonthAdjusted zlicza liczbę dni w miesiącu, w zależności od funkcji kalendarza i tego, czy chcesz uwzględnić dni świąteczne, czy weekendy. Daty mogą być liczone łącznie lub wyłącznie.

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

dalszy: Daty - Dodawanie dni roboczych do daty z wyłączeniem świąt

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

dalszy: Obliczanie dni roboczych między dwiema datami z wyłączeniem świąt w 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

dalszy: Daty - Obliczanie dni roboczych w miesiącu z wyłączeniem świąt

Łączenie danych

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.

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

Data testu

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.

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)