There are a set of problems that require the usage of a series of sequential date and time values. One such problem is to generate reports comprising of sales or events that would capture a sequence of continuous date and time values irrespective of whether a sale or an occurrence of an event happened on a certain date or a time value. Sequential date and time value are also required in applications like schedulers to execute the desired program at a pre-defined date or time value.
There are various ways to generate a sequence of date and time values, but of all that I have come across, the one that leverages the Row Number window function is the most performant and leverages the knowledge of generating sequential numbers that I have already covered in my previous post.
The over-arching concept upon which the generation of date and time values will depend will be the generation of sequential numbers. The pseudo-code will be as follows:
1. Generate sequential numbers between the lower and the upper bound as given. The two bound figures will be the date or time values.
2. Use the DateAdd function to add the generated sequence in step no 1 to lower bound in the unit of time as desired. The unit of time can be minute, hour, day, etc.
First, create a function that can get us a range of sequential numbers between the lower and upper bound:
-- Function to return a table of numbers between lowerbound and upperbound
Create Function dbo.GetNumbers(@LowerBound As Bigint, @UpperBound As Bigint)
Returns Table
As
Return
-- By default, the CTE will always generate numbers starting with 1
-- going all the way upto 42,99,67,296.
-- SQL Engine is smart and will only generate numbers up until
-- the upperbound when defined
With
-- 2^1 = 2 values
Level0 As (Select 1 as SeedValue1 Union All Select 1 As SeedValue2),
-- 2^2 = 4 values
Level1 As (Select 1 As GeneratedValues From Level0 As A
Cross Join Level0 As B),
-- 2^4 = 16 values
Level2 As (Select 1 As GeneratedValues From Level1 As A
Cross Join Level1 As B),
-- 2^8 = 256 values
Level3 As (Select 1 As GeneratedValues From Level2 As A
Cross Join Level2 As B),
-- 2^16 = 65,536 values
Level4 As (Select 1 As GeneratedValues From Level3 As A
Cross Join Level3 As B),
-- 2^32 = 42,99,67,296 values
Level5 As (Select 1 As GeneratedValues From Level4 As A
Cross Join Level4 As B),
-- Obtain unique integers
Numbers As (Select Row_Number() Over(Order By GeneratedValues) As
GeneratedValues From Level5)
Then depending upon the unit of time desired (day, hour, minute etc.), use the following query to generate a sequence of date and time values:
/*
Example: To generate consecutive dates between two given dates,
we will first generate the total gap in terms of the
number of days (sequence of numbers) between the given
bounds and then use that sequence and add the days to
the lower bound to reach the upper bound in a continuous manner.
*/
Declare
@Start as Date = '20210101',
@End as Date = '20210131'
Select Dateadd(day, GeneratedNumbers, @Start) as Dates
From dbo.GetNumbers(0, DateDiff(day, @Start, @End))
Figure 1: Generation of sequential dates between a lower and upper bounds
Here’s a query that generates hour values:
/*
Example: To generate consecutive 12-hour units between the provided bounds,
we will first generate a sequence consisting of the total number of such
units between the provided bounds. Then add units progressively
to the lower bound to reach the upper bound in a continuous manner.
*/
Declare
@Start as Datetime2 = '2021-01-01 00:00:00',
@End as Datetime2 = '2021-01-31 00:00:00'
Select Dateadd(hour, GeneratedNumbers*12, @start) As TimeSlots
from dbo.GetNumbers(0, datediff(hour, @Start, @End)/12)
Figure 2 Generation of sequential units of 12 hours each between a lower and upper bounds
Hope this article was useful.
References:
1. T-SQL Window Functions: For data analysis and beyond (Developer Reference) by Itzik Ben-Gan
2. Common Table Expressions in SQL Server
3. Window Functions in SQL Server
4. Row_Number Window Function in SQL Server