MadeiraToolbox/Utility Scripts/GeneratePeriodsByRange_MultiLineFunc.sql at master · bornsql/MadeiraToolbox · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Date Created: 2021-04-22
Description:
	Multi-Line Table Function to generate periods for time series, based on a range and period type.

Supported period types:
	MI - Minute
	H - Hour
	D - Day
	W - Week
	M - Month
	Q - Quarter
	T - Trimester
	HY - Half-Year
	Y - Year
	Else, the function will attempt to convert the period type parameter to a DATETIME interval.

Example Usage:

	-- Hourly periods
	SELECT *
	FROM [dbo].[GeneratePeriods](CONVERT(DATE, GETDATE()-1), GETDATE(), 'H')

	-- Using a datetime interval
	SELECT *
	FROM [dbo].[GeneratePeriods](CONVERT(DATE, GETDATE()-1), GETDATE(), '00:20:00')

	-- Based on time range:
	DECLARE @FromDate DATETIME = '2020-01-01', @ToDate DATETIME = '2020-05-01'

	SELECT *
	FROM [dbo].[GeneratePeriods](@FromDate, @ToDate, 'D')

	-- Joining with data from a table:
	SELECT
		per.StartDate,
		TotalCount = COUNT(dat.datetimeColumn),
		TotalSum = SUM(dat.amount)
	FROM dbo.MyTable AS dat
	RIGHT JOIN [dbo].[GeneratePeriods](@FromDate, @ToDate, 'D') AS per
	ON dat.datetimeColumn >= per.StartDate
	AND dat.datetimeColumn < per.EndDate
	GROUP BY
		per.StartDate

	-- Generating using data in a table:
	SELECT
		per.StartDate,
		TotalCount = COUNT(dat.datetimeColumn),
		TotalSum = SUM(dat.amount)
	FROM dbo.MyTable AS dat
	CROSS APPLY [dbo].[GeneratePeriods](dat.FromDate, dat.ToDate, 'D') AS per
	GROUP BY
		per.StartDate
*/
CREATE OR ALTER FUNCTION [dbo].[GeneratePeriods]
(
	@FromDate DATETIME,
	@EndDate DATETIME,
	@PeriodType VARCHAR(36)
)
RETURNS @T TABLE(PeriodNum INT, StartDate DATETIME, EndDate DATETIME)
WITH SCHEMABINDING
AS
BEGIN
	WITH Periods
	AS
	(
		SELECT
			PeriodNum = 1,
			StartDate = @FromDate,
			EndDate =
				CASE @PeriodType
					WHEN 'MI' THEN
						DATEADD(minute,1,@FromDate)
					WHEN 'H' THEN
						DATEADD(hh,1,@FromDate)
					WHEN 'D' THEN
						DATEADD(dd,1,@FromDate)
					WHEN 'W' THEN
						DATEADD(ww,1,@FromDate)
					WHEN 'M' THEN
						DATEADD(mm,1,@FromDate)
					WHEN 'Q' THEN
						DATEADD(Q,1,@FromDate)
					WHEN 'T' THEN
						DATEADD(mm,4,@FromDate)
					WHEN 'HY' THEN
						DATEADD(mm,6,@FromDate)
					WHEN 'Y' THEN
						DATEADD(yyyy,1,@FromDate)
					ELSE
						TRY_CONVERT(datetime, @PeriodType) + @FromDate
				END

		UNION ALL

		SELECT
			PeriodNum = PeriodNum + 1,
			StartDate = EndDate,
			EndDate =
				CASE @PeriodType
					WHEN 'MI' THEN
						DATEADD(minute,1,EndDate)
					WHEN 'H' THEN
						DATEADD(hh,1,EndDate)
					WHEN 'D' THEN
						DATEADD(dd,1,EndDate)
					WHEN 'W' THEN
						DATEADD(ww,1,EndDate)
					WHEN 'M' THEN
						DATEADD(mm,1,EndDate)
					WHEN 'Q' THEN
						DATEADD(Q,1,EndDate)
					WHEN 'T' THEN
						DATEADD(mm,4,EndDate)
					WHEN 'HY' THEN
						DATEADD(mm,6,EndDate)
					WHEN 'Y' THEN
						DATEADD(yyyy,1,EndDate)
					ELSE
						TRY_CONVERT(datetime, @PeriodType) + EndDate
				END
		FROM
			Periods
		WHERE
			EndDate < @EndDate
	)
	INSERT INTO @T(PeriodNum, StartDate, EndDate)
	SELECT PeriodNum, StartDate, EndDate
	FROM Periods
	OPTION (MAXRECURSION 0);

	RETURN;
END