SQL Server - Funciones de fecha flexible y tabla de calendario

Compartimos algunas de nuestras funciones con una tabla de calendario para almacenar y calcular festivos y fines de semana, así como establecer frecuencias recurrentes.

Acerca de

Uso de funciones de fecha de SQL Server.png

La mayor parte del trabajo que hemos realizado anteriormente ha consistido en trabajar con fechas, edades y otros datos diversos. Por ejemplo, calcular la edad no es tan sencillo como parece. Hemos escrito una selección de artículos en el pasado, pero ahora estamos reemplazándolos por un par de funciones más flexibles para así reducir el número de funciones que se necesitan.

En este artículo vamos a configurar nuestra tabla de calendario reutilizable y la tabla de días festivos, para luego rellenarla. Las distintas funciones se escribirán en artículos separados en los que podrás obtener más información.

Tendemos a utilizar una base de datos compartida "Utility" con todas nuestras funciones en ella para reducir las necesidades de control de cambios y datos consistentes.

Crear una base de datos

Si tienes una base de datos compartida, o ya sabes dónde quieres almacenar estas funciones entonces adelante, aunque todo el código de aquí ha sido diseñado para ejecutarse en un único script en SQL Server 2016.

Nota. - Puede ser más fácil crear la base de datos en SSMS

¡Consejo! - Configura las opciones en simple para evitar la sobrecarga del archivo de registro y es menos probable que los datos sean críticos para el negocio.

SQL

CREATE DATABASE [Utilities]    CONTAINMENT = NONE ON PRIMARY (    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

Crear Schema

Para facilitar la gestión, hemos creado un esquema llamado Fechas, y almacenamos en él toda nuestra información relacionada. Considera el esquema en este sentido como un contenedor o espacio de nombres para objetos relacionados.

¡Consejo! - Cuando crees un esquema en instrucciones SQL por lotes, envuélvelo en una instrucción sp_executesql para evitar que el sistema se queje de que es la única instrucción del lote. Si existe, envuélvelo en un bloque try para permitir que el código se ejecute.

SQL

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

Funciones compartidas

Hay algunas funciones compartidas que se utilizarán más adelante, por lo que vamos a proporcionar el código y el enlace, con una explicación en cada página. Hay que tener en cuenta ciertas cuestiones:

Los cálculos de año estándar de DateDiff son puramente año-año, así que para calcular la edad necesitas comprobar si esa fecha es mayor o no. La función GetLeapYear era la forma más sensata de averiguar si necesitábamos añadir un día.

La fecha de Pascua puede fallar si no se utiliza la nueva función DateFromParts, debido a la falta de relleno de días.

GetLeapYear

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

Más: Averiguar si el año es bisiesto

GetAge

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(@Until)=1 AND DATEPART(DAYOFYEAR,@Until)>59 THEN -1 ELSE 0 END))THEN -1 ELSE 0 END)RETURN @AgeENDGO

Más: Averiguar edad

DatePad

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

Más: Función de relleno de fecha

DateFromParts

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

Más: Función DATEFROMPARTS de SQL Server para versiones anteriores a 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 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 % 4SET @f = floor((@b+ 8) / 25)SET @g = floor((@b- @f + 1)/3)SET @h = (19*@a+ @b - @d - @g + 15) % 30SET @i = floor(@c/ 4)SET @k = @c % 4SET @L = (32 + 2*@e + 2*@i- @h - @k) % 7SET @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 + 1SET @GregorianEaster = Dates.DateFromParts(@Year,@Month,@Day)RETURN @GregorianEaster ENDGO

Tabla de frecuencia

Para la programación y la recurrencia, utilizamos nuestra propia tabla de programación. Esto nos permite calcular con qué frecuencia debe repetirse algo.

Frecuencia del calendario

CREATE TABLE Dates.CalendarFrequency(FrequencyID NVARCHAR(2),FrequencyName NVARCHAR(100),FrequencyOrder INT)GOINSERT INTO Dates.CalendarFrequencySELECT '','Once',0 UNIONSELECT '7','Weekly',1 UNIONSELECT '14','Fortnightly',2 UNIONSELECT '32','Monthly (Same Day 1st,15th,28th etc)',3  UNIONSELECT '1','Monthly (First Monday,Wednesday etc)',4 UNIONSELECT '2','Monthly (Second Monday,Wednesday etc)',5 UNIONSELECT '3','Monthly (Third Monday,Wednesday etc)',6 UNIONSELECT '4','Monthly (Fourth Monday,Wednesday etc)',10 UNIONSELECT '-1','Monthly (Last Monday,Wednesday etc)',7 UNIONSELECT '-2','Monthly (Second Last Monday,Wednesday etc)',8 UNIONSELECT '-3','Monthly (Third Last Monday,Wednesday etc)',9 UNIONSELECT '-4','Monthly (Fourth Last Monday,Wednesday etc)',11ORDER BY 3GO

Tabla de calendario

Nuestra tabla de calendario está configurada para funcionar durante cualquier periodo de tiempo dentro del intervalo DATETIME2. Para un uso general, el siguiente código te dará un rango que cubrirá la mayoría de los casos.

CalendarCA y CalendarCD son los números de semana ascendentes y descendentes de cada mes, y se utilizan en una referencia cruzada con nuestra tabla de frecuencias anterior.

Crear tabla de calendario

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

Tabla de festivos

Hemos diseñado la tabla de festivos para que sea lo más flexible posible y permita almacenar diferentes opciones añadiendo la columna Función de calendario. A continuación hemos almacenado los festivos de Inglaterra y Gales, Escocia e Irlanda del Norte por separado, haciendo referencia cruzada a la tabla de calendario ya creada.

En teoría puede tener tantos esquemas como se quiera.

Crear tabla de festivos

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.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/*Scotish  Holidays*/INSERT INTO Dates.CalendarHolidaysSELECT CalendarDate,1,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1AND DATEPART(DAY,CalendarDate)=1 UNION --New Years DaySELECT CalendarDate,1,'2nd' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=2 UNION --New Years DaySELECT CalendarDate,1,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate)))UNION--Good FridaySELECT CalendarDate,1,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May HolidaysSELECT CalendarDate,1,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCA=1) UNION--August HolidaysSELECT CalendarDate,1,'St Andrews' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=11 AND DATEPART(DAY,CalendarDate)=30 UNION --St AndrewsSELECT CalendarDate,1,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas DaySELECT CalendarDate,1,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing DayGO/*Northern Irish Holidays*/INSERT INTO Dates.CalendarHolidaysSELECT CalendarDate,2,'New Years Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=1 AND DATEPART(DAY,CalendarDate)=1 UNION --New Years DaySELECT CalendarDate,2,'Good Friday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,-2,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Good FridaySELECT CalendarDate,2,'Easter Monday' FROM Dates.Calendar WHERE CalendarDate=DATEADD(DAY,1,Dates.GetEasterDate(DATEPART(YEAR,CalendarDate))) UNION--Easter MondaySELECT CalendarDate,2,'May Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=5 AND WeekDayID=2 AND (CalendarCA=1 OR CalendarCD=1)UNION--May HolidaysSELECT CalendarDate,2,'Battle of the Boyne' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=7 AND DATEPART(DAY,CalendarDate)=12 UNION --Battle of the BoyneSELECT CalendarDate,2,'August Holidays' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=8 AND WeekDayID=2 AND (CalendarCA=1) UNION--August HolidaysSELECT CalendarDate,2,'Christmas Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=25 UNION --Christmas DaySELECT CalendarDate,2,'Boxing Day' FROM Dates.Calendar WHERE DATEPART(MONTH,CalendarDate)=12 AND DATEPART(DAY,CalendarDate)=26 --Boxing DayGO

Bits inteligentes

Se utilizan tres funciones que las combinan de diversas maneras, todas con opciones muy similares.

GetDateAdjusted adelanta o atrasa la fecha un número determinado de días, dependiendo de la función de calendario y de si deseas incluir festivos o fines de semana.
GetDaysAdjusted cuenta el número de días entre dos fechas, dependiendo de la función de calendario y de si deseas incluir festivos o fines de semana. Las fechas pueden contarse de forma inclusiva o exclusiva.
GetMonthAdjusted cuenta el número de días de un mes, dependiendo de la función de calendario y de si deseas incluir festivos o fines de semana. Las fechas pueden contarse de forma inclusiva o exclusiva.
GetMonthDay devuelve un día concreto del mes, primero, segundo, último, etc.

GetDateAdjusted

CREATE FUNCTION Dates.GetDateAdjusted(@AdjustDate AS DATETIME2,@CalendarFunction INT,@AdjustDays AS INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT) RETURNS DATETIME2 AS BEGINSELECT @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/*Add Days*/WHILE @AdjustMode=0 AND @AdjustWorkDays < @AdjustDaysBEGIN 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 ENDEND/*Subtract Days*/WHILE @AdjustMode=1 AND @AdjustWorkDays > @AdjustDaysBEGIN 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 ENDENDRETURN @DateEND    GO

Añade días laborables a la fecha en una función de SQL Server flexible

GetDaysAdjusted

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))+@AdjustModeENDGO

Cálculo de los días laborables entre dos fechas excluyendo festivos en SQL Server

GetMonthAdjusted

CREATE FUNCTION Dates.GetMonthAdjusted(@Month As DATETIME2,@CalendarFunction INT,@AdjustMode BIT,@AdjustWeekEnds BIT,@AdjustHolidays BIT)RETURNS INT AS BEGINDECLARE @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=@StartDateWHILE @Date < @EndDateBEGINIF ((DATEPART(WEEKDAY,@Date) IN(1,7) AND @AdjustWeekEnds=1)OREXISTS (SELECT * FROM Dates.CalendarHolidays WHERE CalendarDate=@Date AND CalendarFunction=@CalendarFunction AND @AdjustHolidays=1))BEGINSELECT @Count = @Count + 1ENDSET @Date=DATEADD(DAY, 1,@Date)ENDRETURN (DATEDIFF(DAY,@StartDate,@EndDate)-(@Count))+@AdjustModeEND

Cuente los días en un mes en una función de SQL Server con opciones flexibles

GetMonthDay

CREATE FUNCTION dbo.GetMonthDay(@Month DATETIME,@Type INT, @Counter INT) RETURNS DATETIME AS BEGINSET @Month = CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@Month),@Month))DECLARE @CurDate DATETIME=@Month,@Date DATETIME,@Matches INT=0,@TempDate DATETIMEWHILE @CurDateIF (SELECT (CASE @Type WHEN 1 THEN (CASE WHEN DATEPART(WeekDay,@CurDate)=1 THEN 1 ELSE 0 END)WHEN 2 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=2 THEN 1 ELSE 0 END)WHEN 3 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=3 THEN 1 ELSE 0 END)WHEN 4 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=4 THEN 1 ELSE 0 END)WHEN 5 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=5 THEN 1 ELSE 0 END)WHEN 6 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=6 THEN 1 ELSE 0 END)WHEN 7 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=7 THEN 1 ELSE 0 END)WHEN 8 THEN(CASE WHEN DATEPART(WeekDay,@CurDate) IN (1,2,3,4,5,6,7) THEN 1 ELSE 0 END)WHEN 9 THEN(CASE WHEN DATEPART(WeekDay,@CurDate) IN (2,3,4,5,6) THEN 1 ELSE 0 END)WHEN 10 THEN (CASE WHEN DATEPART(WeekDay,@CurDate) IN (1,7) THEN 1 ELSE 0 END)ELSE 0 END))=1 BEGINSET @Matches = @Matches+1SET @TempDate = @CurDateENDIF @Matches=@Counter AND @Counter<32 BEGINSET @Date=@TempDateENDSET @CurDate=DATEADD(DAY,1,@CurDate)ENDRETURN ISNULL(@Date,@TempDate)ENDGO

Función para devolver el día específico del mes

Uniendo los datos

Si bien puede agregar una combinación en su declaración de selección, hemos encontrado que es más fácil hacer referencia en otra función. Al agregar dos fechas, la frecuencia y el número de semana mensual ascendente y descendente, se pueden verificar las fechas relevantes para ver si coinciden.

DateJoin Function

CREATE FUNCTION Dates.DateJoin(@CalendarDate DATETIME,@CrossoverDate DATETIME,@Frequency INT,@WeekAsc INT,@WeekDesc INT) RETURNS BIT AS BEGINDECLARE @J BIT=0/*Once*/IF @CrossoverDate=@CalendarDate SET @J=1/*Weekly/Fornightly*/IF @J=0 AND @Frequency IN (7,14) BEGINIF DATEPART(WEEKDAY,@CrossoverDate)=DATEPART(WEEKDAY,@CalendarDate) AND DATEDIFF(DAY,@CrossoverDate,@CalendarDate)%@Frequency=0SET @J=1END/*Monthly*/IF @J=0 AND @Frequency IN (32) BEGINIF (DATEPART(DAY,@CalendarDate)=DATEPART(DAY,@CrossoverDate)) OR (DATEPART(MONTH,DATEADD(DAY,1,@CalendarDate))<>DATEPART(MONTH,@CalendarDate)ANDDATEPART(MONTH,DATEADD(DAY,1,DATEADD(MONTH,DATEDIFF(MONTH,@CrossoverDate,@CalendarDate),@CrossoverDate)))<>DATEPART(MONTH,DATEADD(MONTH,DATEDIFF(MONTH,@CrossoverDate,@CalendarDate),@CrossoverDate)))SET @J=1END/*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=1RETURN @JENDGO

Unión de todo: datos de prueba

Ahora podemos probar estos datos generando una tabla de programación falsa.

En nuestro ejemplo, se crea un informe para el primero de enero de 2017 y se repite todos los meses el primero con todas las acciones de fecha posibles disponibles.

Mirando el 01/01/2018 de forma aislada, el comportamiento de los fines de semana y los días festivos podría cambiar el rango de fechas entre el 29/12/2017 y el 01/02/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))GOINSERT 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,''GOSELECT (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,FrequencyNameFROM (SELECT Dates.GetDateAdjusted(CalendarDate,0,0,ReportAdjustBehaviour,ReportAdjustWeekend,ReportAdjustHoliday) DateAdjusted,c.CalendarDate,WeekDayName,s.*,f.FrequencyNameFROM (SELECT * FROM Dates.Calendar WHERE CalendarDate BETWEEN '2018-01-01' AND '2018-12-31') cINNER 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) xORDER BY CalendarDate,DateAdjusted,ReportName

Resultados

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)