Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin

EitanBlumin/GeneratePeriods_Inline.sql

Last active November 16, 2021 08:57
Show Gist options
  • Save EitanBlumin/164a41702d8e327ea9db1fbd4a4bc7c4 to your computer and use it in GitHub Desktop.
Save EitanBlumin/164a41702d8e327ea9db1fbd4a4bc7c4 to your computer and use it in GitHub Desktop.
Download ZIP
Table Function to generate periods for time series, based on an end date, period type, and number of periods back
Raw
GeneratePeriods_Inline.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Date Created: 2013-09-01
Last Update: 2020-07-28
Description:
CTE-based Inline Table Function to generate periods for time series, based on an end date, period type, and number of periods back.
Supported period types:
MI - Minute
H - Hour
D - Day
W - Week
M - Month
Q - Quarter
T - Trimester
HY - Half-Year
Y - Year
Example Usage:
-- Based on number of periods:
SELECT *
FROM [dbo].[GeneratePeriods](GETDATE(), 'H', 14)
-- Based on time range:
DECLARE @FromDate DATETIME = '2020-01-01', @ToDate DATETIME = '2020-05-01'
SELECT *
FROM [dbo].[GeneratePeriods](@ToDate, 'D', DATEDIFF(dd, @FromDate, @ToDate) - 1)
OPTION (MAXRECURSION 0)
-- 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](GETDATE(), 'D', 14) AS per
ON dat.datetimeColumn >= per.StartDate
AND dat.datetimeColumn < per.EndDate
GROUP BY
per.StartDate
*/
CREATE OR ALTER FUNCTION [dbo].[GeneratePeriods]
(
@EndDate DATETIME,
@PeriodType CHAR(2),
@NumOfPeriodsBack INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
(
WITH FirstPeriod
AS
(
SELECT
StartDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,DATEDIFF(minute,'2000-01-01',@EndDate),'2000-01-01')
WHEN 'H' THEN
DATEADD(hh,DATEDIFF(hh,'2000-01-01',@EndDate),'2000-01-01')
WHEN 'D' THEN
DATEADD(dd,DATEDIFF(dd,0,@EndDate),0)
WHEN 'W' THEN
DATEADD(day,-1,DATEADD(ww,DATEDIFF(ww,0,@EndDate),0))
WHEN 'M' THEN
DATEADD(mm,DATEDIFF(mm,0,@EndDate),0)
WHEN 'Q' THEN
DATEADD(Q,DATEDIFF(Q,0,@EndDate),0)
WHEN 'T' THEN
CASE
WHEN DATEPART(mm,@EndDate) >= 9 THEN DATEADD(mm,8,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
WHEN DATEPART(mm,@EndDate) >= 5 THEN DATEADD(mm,4,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
ELSE DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0)
END
WHEN 'HY' THEN
CASE WHEN DATEPART(mm,@EndDate) >= 7 THEN DATEADD(mm,6,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
ELSE DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0)
END
WHEN 'Y' THEN
DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0)
END,
EndDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,DATEDIFF(minute,'2000-01-01',@EndDate)+1,'2000-01-01')
WHEN 'H' THEN
DATEADD(hh,DATEDIFF(hh,'2000-01-01',@EndDate)+1,'2000-01-01')
WHEN 'D' THEN
DATEADD(dd,DATEDIFF(dd,0,@EndDate)+1,0)
WHEN 'W' THEN
DATEADD(day,-1,DATEADD(ww,DATEDIFF(ww,0,@EndDate)+1,0))
WHEN 'M' THEN
DATEADD(mm,DATEDIFF(mm,0,@EndDate)+1,0)
WHEN 'Q' THEN
DATEADD(Q,DATEDIFF(Q,0,@EndDate)+1,0)
WHEN 'T' THEN
DATEADD(mm,4,
CASE
WHEN DATEPART(mm,@EndDate) >= 9 THEN DATEADD(mm,8,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
WHEN DATEPART(mm,@EndDate) >= 5 THEN DATEADD(mm,4,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
ELSE DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0)
END)
WHEN 'HY' THEN
CASE WHEN DATEPART(mm,@EndDate) >= 7 THEN DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate)+1,0)
ELSE DATEADD(mm,6,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
END
WHEN 'Y' THEN
DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate)+1,0)
END
), Periods
AS
(
SELECT
PeriodNum = 1,
StartDate,
EndDate
FROM FirstPeriod
UNION ALL
SELECT
PeriodNum = PeriodNum + 1,
StartDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,-1,StartDate)
WHEN 'H' THEN
DATEADD(hh,-1,StartDate)
WHEN 'D' THEN
DATEADD(dd,-1,StartDate)
WHEN 'W' THEN
DATEADD(ww,-1,StartDate)
WHEN 'M' THEN
DATEADD(mm,-1,StartDate)
WHEN 'Q' THEN
DATEADD(Q,-1,StartDate)
WHEN 'T' THEN
DATEADD(mm,-4,StartDate)
WHEN 'HY' THEN
DATEADD(mm,-6,StartDate)
WHEN 'Y' THEN
DATEADD(yyyy,-1,StartDate)
END,
EndDate = StartDate
FROM
Periods
WHERE
PeriodNum < @NumOfPeriodsBack
)
SELECT PeriodNum, StartDate, EndDate
FROM Periods
)
Raw
GeneratePeriods_MultiLineFunc.sql
/*
Author: Eitan Blumin (t: @EitanBlumin | b: https://eitanblumin.com)
Date Created: 2013-09-01
Last Update: 2020-07-28
Description:
Multi-Line Table Function to generate periods for time series, based on an end date, period type, and number of periods back.
Supported period types:
MI - Minute
H - Hour
D - Day
W - Week
M - Month
Q - Quarter
T - Trimester
HY - Half-Year
Y - Year
Example Usage:
-- Based on number of periods:
SELECT *
FROM [dbo].[GeneratePeriods](GETDATE(), 'H', 14)
-- Based on time range:
DECLARE @FromDate DATETIME = '2020-01-01', @ToDate DATETIME = '2020-05-01'
SELECT *
FROM [dbo].[GeneratePeriods](@ToDate, 'D', DATEDIFF(dd, @FromDate, @ToDate) - 1)
-- 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](GETDATE(), 'D', 14) AS per
ON dat.datetimeColumn >= per.StartDate
AND dat.datetimeColumn < per.EndDate
GROUP BY
per.StartDate
*/
CREATE OR ALTER FUNCTION [dbo].[GeneratePeriods]
(
@EndDate DATETIME,
@PeriodType CHAR(2),
@NumOfPeriodsBack INT
)
RETURNS @T TABLE(
PeriodNum INT,
StartDate DATETIME,
EndDate DATETIME)
WITH SCHEMABINDING
AS
BEGIN
WITH FirstPeriod
AS
(
SELECT
StartDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,DATEDIFF(minute,'2000-01-01',@EndDate),'2000-01-01')
WHEN 'H' THEN
DATEADD(hh,DATEDIFF(hh,'2000-01-01',@EndDate),'2000-01-01')
WHEN 'D' THEN
DATEADD(dd,DATEDIFF(dd,0,@EndDate),0)
WHEN 'W' THEN
DATEADD(day,-1,DATEADD(ww,DATEDIFF(ww,0,@EndDate),0))
WHEN 'M' THEN
DATEADD(mm,DATEDIFF(mm,0,@EndDate),0)
WHEN 'Q' THEN
DATEADD(Q,DATEDIFF(Q,0,@EndDate),0)
WHEN 'T' THEN
CASE
WHEN DATEPART(mm,@EndDate) >= 9 THEN DATEADD(mm,8,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
WHEN DATEPART(mm,@EndDate) >= 5 THEN DATEADD(mm,4,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
ELSE DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0)
END
WHEN 'HY' THEN
CASE WHEN DATEPART(mm,@EndDate) >= 7 THEN DATEADD(mm,6,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
ELSE DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0)
END
WHEN 'Y' THEN
DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0)
END,
EndDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,DATEDIFF(minute,'2000-01-01',@EndDate)+1,'2000-01-01')
WHEN 'H' THEN
DATEADD(hh,DATEDIFF(hh,'2000-01-01',@EndDate)+1,'2000-01-01')
WHEN 'D' THEN
DATEADD(dd,DATEDIFF(dd,0,@EndDate)+1,0)
WHEN 'W' THEN
DATEADD(day,-1,DATEADD(ww,DATEDIFF(ww,0,@EndDate)+1,0))
WHEN 'M' THEN
DATEADD(mm,DATEDIFF(mm,0,@EndDate)+1,0)
WHEN 'Q' THEN
DATEADD(Q,DATEDIFF(Q,0,@EndDate)+1,0)
WHEN 'T' THEN
DATEADD(mm,4,
CASE
WHEN DATEPART(mm,@EndDate) >= 9 THEN DATEADD(mm,8,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
WHEN DATEPART(mm,@EndDate) >= 5 THEN DATEADD(mm,4,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
ELSE DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0)
END)
WHEN 'HY' THEN
CASE WHEN DATEPART(mm,@EndDate) >= 7 THEN DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate)+1,0)
ELSE DATEADD(mm,6,DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate),0))
END
WHEN 'Y' THEN
DATEADD(yyyy,DATEDIFF(yyyy,0,@EndDate)+1,0)
END
), Periods
AS
(
SELECT
PeriodNum = 1,
StartDate,
EndDate
FROM FirstPeriod
UNION ALL
SELECT
PeriodNum = PeriodNum + 1,
StartDate =
CASE @PeriodType
WHEN 'MI' THEN
DATEADD(minute,-1,StartDate)
WHEN 'H' THEN
DATEADD(hh,-1,StartDate)
WHEN 'D' THEN
DATEADD(dd,-1,StartDate)
WHEN 'W' THEN
DATEADD(ww,-1,StartDate)
WHEN 'M' THEN
DATEADD(mm,-1,StartDate)
WHEN 'Q' THEN
DATEADD(Q,-1,StartDate)
WHEN 'T' THEN
DATEADD(mm,-4,StartDate)
WHEN 'HY' THEN
DATEADD(mm,-6,StartDate)
WHEN 'Y' THEN
DATEADD(yyyy,-1,StartDate)
END,
EndDate = StartDate
FROM
Periods
WHERE
PeriodNum < @NumOfPeriodsBack
)
INSERT INTO @T(PeriodNum, StartDate, EndDate)
SELECT PeriodNum, StartDate, EndDate
FROM Periods
OPTION (MAXRECURSION 0);
RETURN;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment