Last active
November 13, 2019 01:38
-
-
Save vpetkovic/5771ff61ecc57b7342da9f5ffbaf572d to your computer and use it in GitHub Desktop.
ROWS UNBOUNDED PRECEDING is the abbreviated version of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This window frame-ing option means that for each row the SUM will include itself and all predecessors as ordered by id. The ROWS option is pote
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE FUNCTION dbo.fn_recurringDates( | |
@wkDayPattern tinyint=127, --- 1=Mon, 2=Tue, 4=Wed, ... 127=All | |
@dayFrequency tinyint=1, --- 1=All, 2=every second, ... | |
@exactDay tinyint=NULL, --- Specific day number of the month | |
@occurrenceNo tinyint=NULL, -- 1=First, 2=Second, ... 0=Last | |
@occurrenceType tinyint=NULL, -- ... of 1=Week, 2=Month, 3=Year | |
@weekFrequency tinyint=1, --- 1=Every week, 2=Every second, etc | |
@exactWeek tinyint=NULL, -- Specific ISO week of the year | |
@monPattern smallint=4095, -- 1=Jan, 2=Feb, 4=March, ... | |
@monFrequency tinyint=1, --- 1=Every month, 2=Every second... | |
@yearFrequency tinyint=1, --- 1=Every year, 2=Every two... | |
@start date, --- Start date of recurrence | |
@end date=NULL, --- End date of recurrence | |
@occurrences int=NULL --- Max number of occurrences | |
) | |
RETURNS @dates TABLE ( | |
[date] date NOT NULL, | |
PRIMARY KEY CLUSTERED ([date]) | |
) | |
AS | |
BEGIN | |
--- Variable declarations: | |
DECLARE @occurrenceCount int=0, @year date=@start; | |
--- Make sure the parameters are set correctly: | |
IF (@occurrences IS NULL AND @end IS NULL) RETURN; | |
IF (@occurrenceNo IS NOT NULL AND @occurrenceType IS NULL) | |
SET @occurrenceNo=NULL; | |
--- This loop will start off with @year=@start and then | |
--- increase @year by one calendar year for every iteration: | |
WHILE (@occurrenceCount<@occurrences AND | |
DATEDIFF(yy, @start, @year)<@yearFrequency*@occurrences OR | |
@year<@end) BEGIN; | |
--- Build a recursive common table expression that loops | |
--- through every date from @year and one year forward. | |
WITH dates ([date], occurrence) | |
AS ( | |
SELECT @year, 1 | |
UNION ALL | |
SELECT DATEADD(dd, 1, [date]), occurrence+1 | |
FROM dates | |
WHERE DATEADD(dd, 1, [date])<DATEADD(yy, 1, @year)) | |
--- INSERT the result into the output table, @dates | |
INSERT INTO @dates ([date]) | |
SELECT [date] | |
FROM ( | |
SELECT [date], | |
--- The "ordinal number of the year" | |
DATEDIFF(yy, @start, @year) AS yearOrdinal, | |
--- The ordinal number of the week (first week, | |
--- second, third, ...) starting with @year. | |
DENSE_RANK() OVER ( | |
ORDER BY DATEPART(yy, [date]), | |
NULLIF(DATEPART(isoww, [date]), 0) | |
) AS wkOrdinal, | |
--- Ordinal number of the month, as of @year. | |
DENSE_RANK() OVER ( | |
ORDER BY DATEPART(yy, [date]), DATEPART(mm, [date]) | |
) AS monOrdinal, | |
--- Ordinal number of the day, as of @year. | |
ROW_NUMBER() OVER ( | |
PARTITION BY DATEPART(yy, [date]) | |
ORDER BY [date] | |
) AS dayOrdinal, | |
--- Ordinal number of the day, per @occurenceType, | |
--- as of @year: | |
ROW_NUMBER() OVER ( | |
PARTITION BY (CASE @occurrenceType | |
WHEN 1 THEN DATEPART(isoww, [date]) | |
WHEN 2 THEN DATEPART(mm, [date]) | |
END), | |
(CASE WHEN @occurrenceType IN (1, 3) | |
THEN DATEPART(yy, [date]) END) | |
ORDER BY [date] | |
) AS dateOrdinal, | |
--- dayOrdinal (descending). Used to calculate | |
--- LAST occurrence (@occurenceNo=0) | |
ROW_NUMBER() OVER ( | |
PARTITION BY (CASE @occurrenceType | |
WHEN 1 THEN DATEPART(isoww, [date]) | |
WHEN 2 THEN DATEPART(mm, [date]) | |
END), | |
(CASE WHEN @occurrenceType IN (1, 3) | |
THEN DATEPART(yy, [date]) END) | |
ORDER BY [date] DESC | |
) AS dateOrdinalDesc | |
FROM dates | |
WHERE | |
--- Logical AND to filter specific weekdays: | |
POWER(2, (DATEPART(dw, [date])+@@DATEFIRST+5)%7) | |
& @wkDayPattern>0 AND | |
--- Logical AND to filter specific months: | |
POWER(2, DATEPART(mm, [date])-1) | |
& @monPattern>0 AND | |
--- Filter specific ISO week numbers: | |
(@exactWeek IS NULL OR | |
DATEPART(isoww, [date])=@exactWeek) AND | |
--- Filter specific days of the month: | |
(@exactDay IS NULL OR | |
DATEPART(dd, [date])=@exactDay) | |
) AS sub | |
WHERE | |
--- Modulo operator, to filter yearly frequencies: | |
sub.yearOrdinal%@yearFrequency=0 AND | |
--- Modulo operator, to filter monthly frequencies: | |
sub.monOrdinal%@monFrequency=0 AND | |
--- Modulo operator, to filter weekly frequencies: | |
sub.wkOrdinal%@weekFrequency=0 AND | |
--- Modulo operator, to filter daily frequencies: | |
sub.dateOrdinal%@dayFrequency=0 AND | |
--- Filter day ordinal: | |
(@occurrenceNo IS NULL OR | |
@occurrenceNo=sub.dateOrdinal OR | |
@occurrenceNo=0 AND sub.dateOrdinalDesc=1) AND | |
--- ... and finally, stop if we reach @end: | |
sub.[date]<=ISNULL(@end, sub.[date]) | |
--- The default is 100, so we'll get an error if we don't | |
--- explicitly allow for more recursions: | |
OPTION (MAXRECURSION 366); | |
--- Add the number of dates that we've added to the | |
--- @dates table to our counter, @occurrenceCount. | |
--- Also, increase @year by one year. | |
SELECT | |
@occurrenceCount=@occurrenceCount+@@ROWCOUNT, | |
@year=DATEADD(yy, 1, @year); | |
END; | |
RETURN; | |
END; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment