Oftentimes you’ll need to generate a table of time slots to use in much the same way as a Calendar table. You might want to use this to do aggregations for reports, such as the amount of trading by the time of day, and for doing this a table of time-slots within a day makes it easy, and more flexible. You can, of course, do this without any Time-Slot tables until things get more complicated, as when the business only runs two shifts every twenty-four hours, and you only need the time slots from 05:00 to 20:00 (sixteen time slots with eight hours in each shift), or when you need to generate time slots of over an hour (like every four hours) or minutes, etc.
“Generate them when you need them.”
— SQL MVP Jeff Moden on time slots
Indeed, since we’re all probably familiar with Tally tables, whether in a physical table or one that is generated in-line, this is relatively easy to accomplish.
Using Time-slots: A Real World Example
Suppose we run a manufacturing business that has three machines producing widgets. Each machine produces a discrete quantity of widgets, but spits out that discrete quantity at irregular intervals. Let’s create some sample data for this.
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 |
-- A table holding our widget-producing machines CREATE TABLE dbo.Machines ( MachineID SMALLINT IDENTITY PRIMARY KEY ,MachineName VARCHAR(30) ); GO -- Sample data for the three widget-producing machines INSERT INTO dbo.Machines (MachineName) VALUES('Widget Maker 1'),('Widget Maker 2'),('Widget Maker 3'); GO -- A table holding our production results CREATE TABLE dbo.MachineProduction ( MachineID SMALLINT ,TimeProduced DATETIME ,QtyOfWidgets INT ,PRIMARY KEY (MachineID, TimeProduced) ); GO -- Sample data for production results WITH ProductionDays (pd) AS ( -- Three days worth SELECT '2015-04-01' UNION ALL SELECT '2015-04-02' UNION ALL SELECT '2015-04-03' ), Tally (n) AS ( -- A Tally table that produces 1,000 rows SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n) CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) ) INSERT INTO dbo.MachineProduction ( MachineID, TimeProduced, QtyOfWidgets ) SELECT MachineID, TimeProduced -- Random number of widgets produced (between 1 and 20) ,1+ABS(CHECKSUM(NEWID()))%20 FROM ( -- Use DISTINCT here to avoid PRIMARY KEY duplicates SELECT DISTINCT MachineID -- We'll add to our production date (pd) a random number of seconds between -- 1 and 86,400 (number of seconds in a day) to get a discrete time of day, -- then create a Cartesian product against this and our production machines ,TimeProduced=DATEADD(second, ABS(CHECKSUM(NEWID()))%86400, pd) FROM ProductionDays a CROSS APPLY Tally b CROSS APPLY (VALUES(1),(2),(3)) c (MachineID) ) a -- Create some gaps where the machines were down (not producing anything) on those days WHERE NOT ((MachineID = 1 AND DATEADD(day, DATEDIFF(day, 0, TimeProduced), 0) = '2015-04-02') OR (MachineID = 2 AND DATEADD(day, DATEDIFF(day, 0, TimeProduced), 0) = '2015-04-01')); |
Note that the WHERE filter creates some gaps simulating a case where MachineID=1 was not operating on 2015-04-02 and MachineID=2 was not operating on 2015-04-01.
Now suppose we’d like to track our widgets production every six hours (our time slot increment) for the three days we’ve created our data, including those time slots where a machine produced nothing.
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 |
WITH TimeSlotsOffsets (TimeSlot, StartOffsetHrs, EndOffsetHrs) AS ( -- Six hours at a time SELECT CAST('00:00' AS TIME), 0, 6 UNION ALL SELECT '06:00', 6, 12 UNION ALL SELECT '12:00', 12, 18 UNION ALL SELECT '18:00', 18, 24 ), ProductionDays (pd) AS ( -- Three days worth SELECT '2015-04-01' UNION ALL SELECT '2015-04-02' UNION ALL SELECT '2015-04-03' ) SELECT TimeSlot = [BaseDateWithTimeSlotStart] ,a.MachineID ,QtyofWidgets = ISNULL(b.QtyOfWidgets, 0) FROM ( -- Create Cartesian product SELECT c.MachineID ,[BaseDate] = a.pd ,[BaseDateWithTimeSlotStart] = DATEADD(hour, b.StartOffsetHrs, a.pd) ,[BaseDateWithTimeSlotEnd] = DATEADD(hour, b.EndOffsetHrs, a.pd) -- Production days of interest FROM ProductionDays a -- Time slots of 6 hours CROSS JOIN TimeSlotsOffsets b -- Unique MachineIDs CROSS JOIN dbo.Machines c ) a OUTER APPLY ( -- Sum the widgets produced SELECT b.MachineID, QtyOfWidgets=SUM(b.QtyOfWidgets) FROM dbo.MachineProduction b WHERE a.MachineID = b.MachineID AND -- where production time is within the time slot period b.TimeProduced >= a.[BaseDateWithTimeSlotStart] AND b.TimeProduced < a.[BaseDateWithTimeSlotEnd] GROUP BY b.MachineID ) b ORDER BY MachineID, [BaseDateWithTimeSlotStart]; |
A troubling aspect, to me at least, of the above code is that we have essentially hard-coded our time slots. However it does produce the results we have asked for. In the above, we’ve created a Cartesian product of machines by days by time slots, and then retrieved the total widgets created in each time slot.
Let’s suppose one of our pesky business users comes along and tells us he’d like to change the time slot duration to six hours. Since we hard-coded that the first time, we’d now like to anticipate that he may change it again, and let’s also add something just a bit more complex, such as a running total by machine by day. So we’ll add a local variable that specifies the time slot duration, and use it to dynamically generate our time slots within the TimeSlotsOffsets Common Table Expression (CTE). Other than adding the running total by day, we’ll still calculate the same Cartesian product of machines by days by time slots and we’re good to go.
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 |
DECLARE @TimeSlotHrsDuration SMALLINT = 6; -- 6 hours WITH Tally (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 -- Zero based FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n) ), TimeSlotsOffsets (StartOffsetHrs, EndOffsetHrs) AS ( -- Varying duration for each time slot SELECT n*@TimeSlotHrsDuration, (1+n)*@TimeSlotHrsDuration FROM Tally WHERE n < 24 / @TimeSlotHrsDuration ), ProductionDays (pd) AS ( -- Three days worth SELECT '2015-04-01' UNION ALL SELECT '2015-04-02' UNION ALL SELECT '2015-04-03' ) SELECT TimeSlot = [BaseDateWithTimeSlotStart] ,a.MachineID ,QtyofWidgets = ISNULL(b.QtyOfWidgets, 0) ,DailyQty = ISNULL(SUM(b.QtyOfWidgets) OVER ( PARTITION BY a.MachineID, [BaseDate] ORDER BY [BaseDateWithTimeSlotStart] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), 0) FROM ( -- Create Cartesian product SELECT c.MachineID ,[BaseDate] = a.pd ,[BaseDateWithTimeSlotStart] = DATEADD(hour, b.StartOffsetHrs, a.pd) ,[BaseDateWithTimeSlotEnd] = DATEADD(hour, b.EndOffsetHrs, a.pd) -- Production days of interest FROM ProductionDays a -- Time slots of 6 hours CROSS JOIN TimeSlotsOffsets b -- Unique MachineIDs CROSS JOIN dbo.Machines c ) a OUTER APPLY ( -- Sum the widgets produced SELECT b.MachineID, QtyOfWidgets=SUM(b.QtyOfWidgets) FROM dbo.MachineProduction b WHERE a.MachineID = b.MachineID AND -- where production time is within the time slot period b.TimeProduced >= a.[BaseDateWithTimeSlotStart] AND b.TimeProduced < a.[BaseDateWithTimeSlotEnd] GROUP BY b.MachineID ) b ORDER BY MachineID, [BaseDateWithTimeSlotStart]; |
If you run the setup data and the query, it should produce a results set that looks something like the following. Of course your results will vary due to the random test data that we set up, however you should at least see the same gaps in production due to the machine outages we introduced into the test data.
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 |
TimeSlot MachineID QtyofWidgets DailyQty 2015-04-01 00:00:00.000 1 2226 2226 2015-04-01 06:00:00.000 1 2955 5181 2015-04-01 12:00:00.000 1 2548 7729 2015-04-01 18:00:00.000 1 2622 10351 2015-04-02 00:00:00.000 1 0 0 2015-04-02 06:00:00.000 1 0 0 2015-04-02 12:00:00.000 1 0 0 2015-04-02 18:00:00.000 1 0 0 2015-04-03 00:00:00.000 1 2753 2753 2015-04-03 06:00:00.000 1 2795 5548 2015-04-03 12:00:00.000 1 2555 8103 2015-04-03 18:00:00.000 1 2639 10742 2015-04-01 00:00:00.000 2 0 0 2015-04-01 06:00:00.000 2 0 0 2015-04-01 12:00:00.000 2 0 0 2015-04-01 18:00:00.000 2 0 0 2015-04-02 00:00:00.000 2 2499 2499 2015-04-02 06:00:00.000 2 2608 5107 2015-04-02 12:00:00.000 2 2500 7607 2015-04-02 18:00:00.000 2 2741 10348 2015-04-03 00:00:00.000 2 2550 2550 2015-04-03 06:00:00.000 2 2528 5078 2015-04-03 12:00:00.000 2 2563 7641 2015-04-03 18:00:00.000 2 2676 10317 2015-04-01 00:00:00.000 3 2537 2537 2015-04-01 06:00:00.000 3 2627 5164 2015-04-01 12:00:00.000 3 2508 7672 2015-04-01 18:00:00.000 3 2611 10283 2015-04-02 00:00:00.000 3 2752 2752 2015-04-02 06:00:00.000 3 2614 5366 2015-04-02 12:00:00.000 3 2778 8144 2015-04-02 18:00:00.000 3 2528 10672 2015-04-03 00:00:00.000 3 2500 2500 2015-04-03 06:00:00.000 3 2427 4927 2015-04-03 12:00:00.000 3 2665 7592 2015-04-03 18:00:00.000 3 2599 10191 |
This query generates a total of 36 rows (3 days x 3 machines x 4 time slots per day).
In this article, we’re going to show you how to remove a whole bunch of extraneous code, such as the two CTEs, and minimize the effort to create the Cartesian product across any duration of time slot using a function I’ll call CalculateTimeSlots
, which will be introduced just a bit later.
An In-line Tally Table for Generating Time Slots
To generate time slots of any duration, even down to the second, we can use an in-line Tally table such as the one below.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
WITH Tally (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 -- zero-based -- Returns exactly 86400 rows (number of seconds in a day) FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n) -- 6 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- x10 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- x10 rows ) -- = 86,400 rows SELECT n FROM Tally; |
Our Tally table has been specifically designed to return exactly 86,400 rows, which just happens to correspond to the number of seconds in a day. It is a zero-based Tally table, so we can use it directly as a time offset.
Time Slots – Hours or Minutes in a Day
By adding a TOP clause to our Tally table along with a little DATEADD logic, we can quite easily create any number of time slots that are based on hours, minutes or seconds. The TOP clause will minimize the actual number of rows returned by the Tally table, to just what we need for our time slot duration.
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 |
WITH Tally (n) AS ( SELECT TOP (24) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 -- zero-based -- Returns exactly 86400 rows (number of seconds in a day) FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n) -- 6 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- x10 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- x10 rows ) -- = 86,400 rows -- 24 time slots, one for each hour of the day SELECT TimeSlot=DATEADD(hour, n, CAST('00:00' AS TIME)) FROM Tally; WITH Tally (n) AS ( SELECT TOP (3600) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 -- zero-based -- Returns exactly 86400 rows (number of seconds in a day) FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n) -- 6 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- x10 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- x10 rows ) -- = 86,400 rows -- 3600 time slots, one for each minute of the day SELECT TimeSlot=DATEADD(minute, n, CAST('00:00' AS TIME)) FROM Tally; |
Notice how we’ve adjusted the TOP clause in each case to the number of time slots we want to generate, and then used the return value from the Tally table to add either hours or minutes to midnight.
We could just as easily have adjusted both the base time and the number of time slots (in TOP) to give us our two eight hour shifts that run from 05:00 to 20:00.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
WITH Tally (n) AS ( SELECT TOP (16) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 -- zero-based -- Returns exactly 86400 rows (number of seconds in a day) FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n) -- 6 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- x10 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- x10 rows ) -- = 86,400 rows -- 16 time slots starting at 05:00, one for each hour in two 16 hour shifts SELECT TimeSlot=DATEADD(hour, n, CAST('05:00' AS TIME)) FROM Tally; |
Creating Time Slots across Multiple Days
If we combine our time slot generator with a Calendar table, we can generate our time slots across multiple calendar days. In the next example, I use an in-line, Table Valued Function (iTVF) that appears in the blog entry I linked to earlier about Calendar tables. It is also included in the resources file that appears with this article in case you’d like to follow along and try some of the examples here.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
WITH Tally (n) AS ( SELECT TOP (24) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 -- zero-based -- Returns exactly 86400 rows (number of seconds in a day) FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n) -- 6 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- x10 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- x10 rows ) -- = 86,400 rows -- 24 time slots, one for each hour of the day SELECT TimeSlot=DATEADD(hour, DATEDIFF(hour, 0, DATEADD(hour, n, CAST('00:00' AS TIME))), [Date]) FROM dbo.GenerateCalendar('2015-04-01', 2) a CROSS APPLY Tally b; |
My GenerateCalendar
FUNCTION returns [Date] (actually a DATETIME) as one of its columns. It is necessary to convert the time slot to a time offset in hours using DATEDIFF, and then add this back to the [Date] column to get our time slot within that specific day. The query above returns 48 rows (some omitted for brevity).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
TimeSlot 2015-04-01 00:00:00.000 2015-04-01 01:00:00.000 2015-04-01 02:00:00.000 -- 18 rows redacted 2015-04-01 21:00:00.000 2015-04-01 22:00:00.000 2015-04-01 23:00:00.000 2015-04-02 00:00:00.000 2015-04-02 01:00:00.000 2015-04-02 02:00:00.000 -- 18 rows redacted 2015-04-02 21:00:00.000 2015-04-02 22:00:00.000 2015-04-02 23:00:00.000 |
Another Example: Fifteen Minute Time Slots
Suppose we want to generate time slots that are precisely fifteen minutes apart. We know that there are 1,440 (24 x 60) minutes in a day, so there should be one-fourth this number of time periods if we want them spaced 15 minutes apart (so 96 time slots).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
WITH Tally (n) AS ( SELECT TOP (96) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 -- zero-based -- Returns exactly 86400 rows (number of seconds in a day) FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n) -- 6 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- x10 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- x10 rows ) -- = 86,400 rows -- 3600 time slots, one for each minute of the day SELECT TimeSlot=DATEADD(minute, n*15, CAST('00:00' AS TIME)) FROM Tally; |
We’ve had to do two things to our query: 1) modify the TOP clause to return precisely the correct number of 15 minute time slots we calculated earlier and 2) multiply our Tally return column by that same 15 minutes. The results are as we expect, once again abbreviated for clarity.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
TimeSlot 00:00:00.0000000 00:15:00.0000000 00:30:00.0000000 00:45:00.0000000 01:00:00.0000000 01:15:00.0000000 ... 84 rows redacted 22:30:00.0000000 22:45:00.0000000 23:00:00.0000000 23:15:00.0000000 23:30:00.0000000 23:45:00.0000000 |
Clock Arithmetic is for Geeks, so let’s just Do It Once
I don’t know how all of my readers feel about this, but personally I hate clock arithmetic. All of those magic numbers – seconds in a day, minutes in a day, etc. are just something I hate having to remember all the time. So while Jeff may be right, that you can easily generate time slots of any sort when you need them, I’d prefer to only have to do it once so I don’t have to remember where to divide by 1,440, or do anything else that requires me to think about this for more than a microsecond.
Perhaps it might be possible to construct a nice, tidy little iTVF to satisfy the most common time slot needs. After a bit of fiddling around, I came up with this, which can also be found in the attached resources file.
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 |
CREATE FUNCTION [dbo].[CalculateTimeSlots] -- A function to calculate time slots over a twenty-four hour period ( -- @SlotType: 'hour', 'minute' or 'second' (invalid defaults to 'hour') -- Other allowed abbreviations as in DATEADD/DATEDIFF are also supported -- for these three slot types. @SlotType VARCHAR(6) -- @SlotDuration: Must be a zero remainder divisor of @SlotType -- For @SlotType='second' then when 60%@SlotDuration = 0 the increment is valid -- For @SlotType='minute' then when 60%@SlotDuration = 0 the increment is valid -- For @SlotType='hour' then when 24%@SlotDuration = 0 the increment is valid -- If invalid, defaults to 1 ,@SlotDuration SMALLINT ,@BaseDate DATETIME ,@NumberOfDays INT ) RETURNS TABLE WITH SCHEMABINDING RETURN WITH Tally (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 -- zero-based -- Returns exactly 86400 rows (number of seconds in a day) FROM (VALUES(0),(0),(0),(0),(0),(0)) a(n) -- 6 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n) -- x10 rows CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e(n) -- x10 rows ), -- = 86,400 rows MagicNumbersForHMS AS ( -- All clock arithmetic magic numbers are consolidated here to drive the calculations -- that will follow. SELECT SlotType = 'second', SlotOffset=1, UnitsPerNext=60, UnitsPerDay=86400 UNION ALL SELECT 'minute', 60, 60, 1440 UNION ALL SELECT 'hour', 3600, 24, 24 ), MiscParameters (t, SlotOffset, SlotDuration, TimeSlots, BaseDate, NoDays) AS ( -- Start with the base offset as a TIME datatype SELECT CAST('00:00' AS TIME) ,b.SlotOffset -- Set SlotDuration to 1 if not evenly divisible by specified @SlotDuration ,CASE UnitsPerNext % @SlotDuration WHEN 0 THEN @SlotDuration ELSE 1 END -- Calculate the number of time slots for this call ,CASE WHEN @SlotDuration <= 0 OR UnitsPerNext%@SlotDuration <> 0 THEN UnitsPerDay ELSE UnitsPerDay / ISNULL(@SlotDuration, 1) END -- Pass the BaseDate forward ,@BaseDate -- Calculate the number of days (1 if not specified) for the Cartesian product ,CASE WHEN @BaseDate IS NULL OR NULLIF(@NumberOfDays, 0) IS NULL THEN 1 ELSE ABS(@NumberOfDays) END FROM ( -- Default the @SlotType to hours if invalid SELECT SlotType=CASE -- Valid for hours WHEN @SlotType IN ('hour', 'hh') THEN 'hour' -- Valid for minutes WHEN @SlotType IN ('minute', 'mi', 'n') THEN 'minute' -- Valid for seconds WHEN @SlotType IN ('second','ss', 's') THEN 'second' ELSE 'hour' END ) a JOIN MagicNumbersForHMS b ON a.SlotType = b.SlotType ) SELECT [TimeSlot] = DATEADD(second, c.n * a.SlotOffset * a.SlotDuration, t) -- Offset based on @SlotType ,[TimeSlotOffsetUnits] = c.n * a.SlotDuration -- Offset based on seconds ,[TimeSlotOffsetSeconds] = c.n * a.SlotOffset * SlotDuration -- Extended return columns starting with base date ,[BaseDate] = CAST(b.[Date] AS DATE) -- Base date plus offset ,[BaseDateWithTimeSlotStart] = DATEADD(second, c.n*a.SlotOffset*a.SlotDuration, b.[Date]) -- Base date plus offset plus duration ,[BaseDateWithTimeSlotEnd] = DATEADD(second, (c.n+1)*a.SlotOffset*a.SlotDuration, b.[Date]) FROM MiscParameters a -- CROSS APPLY to generate the requisite number of days from the extended parameters -- (1 day/row if not specified) CROSS APPLY ( SELECT TOP (a.NoDays) [Date]=b.n + a.BaseDate FROM Tally b ORDER BY n ) b -- Generate the Cartesian product of days vs. time slots CROSS APPLY ( SELECT TOP (a.TimeSlots) c.n FROM Tally c ORDER BY n ) c; |
We’ve created the arguments to this function based on the premise that your time slots are either in hours, minutes or seconds. You’ll need to do a bit of work if you want to extend it to milliseconds or microseconds.
Let’s describe each of the arguments, including the last two which I call “extended arguments” because of the additional value they add.
@SlotType
(VARCHAR(6)) is passed as “second” for seconds, “minute” for minutes and “hour” for hours. It also supports the standard abbreviation for these “dateparts” as defined for the DATEADD and DATEDIFF T-SQL functions, in the hopes that this makes them easier to remember.@SlotDuration
(SMALLINT) is the duration you want for each time slot. For example, time slots that are in hours where you want 24 of them in a day are each one hour in duration.@SlotDuration
must be specified as an integer that equally divides into the number of units of@SlotType
that are present in the next larger time unit. For example, in a minute there are sixty seconds, so if you specify@SlotType
= ‘second’ then@SlotDuration
must be either 1, 2, 3, 4, 5, 6, 10, 12, 15 or 30 (a number that divides into 60 with no remainder).@BaseDate
(DATETIME) is the first of two extended arguments that can be used if you want your time slots to include not just a time component but also a date component. Coupled with additional return columns, this can be quite useful in cases where you need your time slots generated across many days.@NumberOfDays
(INT) is the second of two extended arguments that (in conjunction with@BaseDate
) specifies the number of days or dates for which you want the time slots enumerated.
We’ll clarify each of these arguments along with the returned columns from the function as we look at some examples, starting with an easy one. The comments in the function hopefully explain what the function does. Using the extended parameters allows you to generate a Cartesian product of days by time slots.
To return 24 time slots of one hour, any of these parameter combinations will work:
1 2 3 4 5 6 7 8 |
SELECT TimeSlot FROM dbo.CalculateTimeSlots(NULL, NULL, NULL, NULL); -- or --FROM dbo.CalculateTimeSlots('hour', NULL, NULL, NULL); -- or --FROM dbo.CalculateTimeSlots('hour', 1, NULL, NULL); -- or --FROM dbo.CalculateTimeSlots('minute', 60, NULL, NULL) a; |
To handle our two eight hour shifts starting at 05:00, we simply add a WHERE filter.
1 2 3 4 5 6 7 |
SELECT TimeSlot FROM dbo.CalculateTimeSlots('hour', 1, NULL, NULL) -- or --FROM dbo.CalculateTimeSlots('hour', NULL, NULL, NULL) -- or --FROM dbo.CalculateTimeSlots('hour', 99, NULL, NULL) WHERE TimeSlot BETWEEN '05:00' AND '20:00'; |
You can try the alternate FROM combinations to see how an invalid slot increment gets translated to one.
To get our 96 time slots at 15 minute intervals, the call is equally simple.
1 2 |
SELECT TimeSlot FROM dbo.CalculateTimeSlots('minute', 15, NULL, NULL); |
We can generate our time slots across two days using our GenerateCalendar function like we did before:
1 2 3 4 5 6 |
-- 24 time slots, one for each hour of the day using GenerateCalendar SELECT TimeSlot1 = DATEADD(hour, DATEDIFF(hour, 0, [TimeSlot]), [Date]) ,TimeSlot2 = DATEADD(hour, TimeSlotOffsetUnits, [Date]) ,TimeSlot2 = DATEADD(second, TimeSlotOffsetSeconds, [Date]) FROM dbo.GenerateCalendar('2015-04-01', 2) a CROSS APPLY dbo.CalculateTimeSlots('hour', 1, NULL, NULL) b; -- 2=hourly time slots |
Or we can simply use the extended call parameters to simplify the query even further. In this case, the four columns all return the same amounts (shown so you’ll understand just how much more simplified this query is).
1 2 3 4 5 6 7 |
-- 24 time slots, one for each hour of the day without GenerateCalendar SELECT BaseDateWithTimeSlotStart -- Use the extended return column -- These calculations could be done but are not necessary ,TimeSlot1 = DATEADD(hour, DATEDIFF(hour, 0, [TimeSlot]), CAST(a.BaseDate AS DATETIME)) ,TimeSlot2 = DATEADD(hour, TimeSlotOffsetUnits, CAST(a.BaseDate AS DATETIME)) ,TimeSlot2 = DATEADD(second, TimeSlotOffsetSeconds, CAST(a.BaseDate AS DATETIME)) FROM dbo.CalculateTimeSlots('hour', 1, '2015-04-01', 2) a; -- 2=hourly time slots/2 days |
To me that looks much easier on the eyes than what we did before! Even with the Timeslot1, TimeSlot2, TimeSlot3 variables which I included for illustration of you can use other columns returned by the FUNCTION.
Calculations vs. Lookups
“Since SQL is a database language, we prefer to do look ups and not calculations.”
– Joe Celko (prolific author of books on things SQL)
Ah yes, the on-going debate on whether we should do calculations or lookups has raised its ugly head yet again!
In that Calendar table link that I gave you earlier and also in another blog (An Easter SQL), we showed how you could do either calculations (if you prefer) or lookups if that’s where your preferences are.
Time slot lookups pose a slightly more challenging conundrum than do holiday tables though. Let’s look at some of the ways we could construct them.
Hourly or By Every Fifteen Minutes (Separate Time Slot Tables)
We could create a separate table for every time slot combination we need for the use-cases in our application.
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 |
-- An hourly time slots table CREATE TABLE dbo.TimeSlots_Hourly ( TimeSlot TIME PRIMARY KEY ); GO INSERT INTO dbo.TimeSlots_Hourly (TimeSlot) SELECT TimeSlot FROM dbo.CalculateTimeSlots('hour', 1, NULL, NULL); SELECT * FROM dbo.TimeSlots_Hourly; GO -- A time slots table with one row for every 15 minutes CREATE TABLE dbo.TimeSlots_QuarterHourly ( TimeSlot TIME PRIMARY KEY ); GO INSERT INTO dbo.TimeSlots_QuarterHourly (TimeSlot) SELECT TimeSlot FROM dbo.CalculateTimeSlots('minute', 15, NULL, NULL); SELECT * FROM dbo.TimeSlots_QuarterHourly; |
That seems a little redundant to me but if it floats your boat, go for it!
Hourly or By Every Fifteen Minutes (A Single Time Slot Table)
An alternative could be to create a generic time slot lookup that handles multiple time slot types, like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Time slots table where SlotType determines type CREATE TABLE dbo.TimeSlots ( SlotType VARCHAR(20) ,TimeSlot TIME ,PRIMARY KEY (SlotType, TimeSlot) ); GO WITH SlotTypes (LookupKey, SlotType, SlotIncrement) AS ( SELECT 'Hourly', 'hour', 1 UNION ALL SELECT 'QtrHourly', 'minute', 15 ) INSERT INTO dbo.TimeSlots (SlotType, TimeSlot) SELECT LookupKey, TimeSlot FROM SlotTypes CROSS APPLY dbo.CalculateTimeSlots(SlotType, SlotIncrement, NULL, NULL); SELECT * FROM dbo.TimeSlots; GO |
Then it just becomes a matter of remembering all of the SlotTypes
you defined.
You could of course use two time slot definition columns as part of the PRIMARY KEY, for example where they are identical to the arguments to the CalculateTimeSlots FUNCTION. We’ll let you decide if that is better than using the FUNCTION directly.
Various Time Slots in Different Columns of a Single Time Slots Table
Another way perhaps, is something like the following where your table rows correspond to time slots by second (the PRIMARY KEY), and other necessary time slots are created as additional columns that are sparsely populated.
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 |
CREATE TABLE dbo.TimeSlots ( TimeSlotsBySecond TIME PRIMARY KEY ,TimeSlotByMinute TIME NULL ,TimeSlotByQtrHour TIME NULL ,TimeSlotByHour TIME NULL ); CREATE NONCLUSTERED INDEX ts_ix1 ON dbo.TimeSlots (TimeSlotByMinute); CREATE NONCLUSTERED INDEX ts_ix2 ON dbo.TimeSlots (TimeSlotByQtrHour); CREATE NONCLUSTERED INDEX ts_ix3 ON dbo.TimeSlots (TimeSlotByHour); GO -- Columns after TimeSlotBySecond are sparsely populated INSERT INTO dbo.TimeSlots ( TimeSlotsBySecond, TimeSlotByMinute, TimeSlotByQtrHour, TimeSlotByHour ) SELECT TimeSlotsBySecond=a.TimeSlot ,TimeSlotByMinute=b.TimeSlot ,TimeSlotByQtrHour=c.TimeSlot ,TimeSlotByHour=d.TimeSlot FROM dbo.CalculateTimeSlots('second', 1, NULL, NULL) a LEFT JOIN dbo.CalculateTimeSlots('minute', 1, NULL, NULL) b ON a.TimeSlot = b.TimeSlot LEFT JOIN dbo.CalculateTimeSlots('minute', 15, NULL, NULL) c ON a.TimeSlot = c.TimeSlot LEFT JOIN dbo.CalculateTimeSlots('hour', 1, NULL, NULL) d ON a.TimeSlot = d.TimeSlot SELECT * FROM dbo.TimeSlots; |
Now I’m not saying I recommend this, but at least with the indexing defined when you do a query such as the following:
1 2 3 |
SELECT TimeSlot=TimeSlotByQtrHour FROM dbo.TimeSlots WHERE TimeSlotByQtrHour BETWEEN '08:00' AND '15:00'; |
You will get a NONCLUSTERED INDEX SEEK in the execution plan.
But this does mean you’d have quite a few INDEXes!
So the choice is up to you: 1) calculate them on the fly with home grown code for each case, 2) use an iTVF to hide the magic of the time slot calculations or 3) choose one of the lookup table examples I’ve provided.
Using Time-slots: A Real World Example (Revisited)
All that is left now is to use our nifty new CalculateTimeSlots function to simplify our real world example.
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 |
DECLARE @TimeSlotHrsDuration SMALLINT = 6; -- 6 hours SELECT TimeSlot = [BaseDateWithTimeSlotStart] ,a.MachineID ,QtyofWidgets = ISNULL(b.QtyOfWidgets, 0) ,DailyQty = ISNULL(SUM(b.QtyOfWidgets) OVER ( PARTITION BY a.MachineID, [BaseDate] ORDER BY [BaseDateWithTimeSlotStart] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), 0) FROM ( -- Create Cartesian product SELECT b.MachineID, a.[BaseDate] ,a.[BaseDateWithTimeSlotStart] ,a.[BaseDateWithTimeSlotEnd] -- Time slots of 6 hours FROM dbo.CalculateTimeSlots('hour', @TimeSlotHrsDuration, '2015-04-01', 3) a -- Unique MachineIDs CROSS JOIN dbo.Machines b ) a OUTER APPLY ( -- Sum the widgets produced SELECT b.MachineID, QtyOfWidgets=SUM(b.QtyOfWidgets) FROM dbo.MachineProduction b WHERE a.MachineID = b.MachineID AND -- where production time is within the time slot period b.TimeProduced >= a.[BaseDateWithTimeSlotStart] AND b.TimeProduced < a.[BaseDateWithTimeSlotEnd] GROUP BY b.MachineID ) b ORDER BY MachineID, [BaseDateWithTimeSlotStart]; |
Notice how both CTEs have been eliminated, and we simplified the section containing the Cartesian product. Obviously, additional local variables can be added or calculated to generate and pass the third and fourth parameters to our iTVF function call.
This is but one of many real world examples where I’ve found it necessary to generate varying durations of time slots across a varying number of days. Sometimes the time slots are by hours, other times by multiple (such as fifteen) minutes, etc. So this is a generalized tool for generating those time slots when you need them.
Conclusions
We’ve described three different ways you can generate fixed duration time slots within a day.
- Generate them with ad-hoc (or home grown) code for the particular case.
- Use the
CalculateTimeSlots
FUNCTION, which allows you to generate the Cartesian product of days by time slots if you need to. - Use a lookup table, for which you can vary the content according to your needs.
While the debate will probably rage on, whether to use lookups vs. calculations, I think my preference in this case is going to be the second alternative (my CalculateTimeSlots
FUNCTION).
We haven’t shown it but your time slots are easily combined with an existing Calendar table (dbo.Calendar) if you have one so that you can filter by date and/or by time slot.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT TimeSlot, BaseDateWithTimeSlotStart, BaseDateWithTimeSlotEnd FROM dbo.Calendar a CROSS APPLY CalculateTimeSlots('hour', 3, a.[Date], 1) b WHERE a.[Date] BETWEEN '2015-01-01' AND '2015-01-05' AND b.TimeSlot BETWEEN '06:00' AND '12:00'; -- Results: TimeSlot BaseDateWithTimeSlotStart BaseDateWithTimeSlotEnd 06:00:00.0000000 2015-01-01 06:00:00.000 2015-01-01 09:00:00.000 09:00:00.0000000 2015-01-01 09:00:00.000 2015-01-01 12:00:00.000 12:00:00.0000000 2015-01-01 12:00:00.000 2015-01-01 15:00:00.000 06:00:00.0000000 2015-01-02 06:00:00.000 2015-01-02 09:00:00.000 09:00:00.0000000 2015-01-02 09:00:00.000 2015-01-02 12:00:00.000 12:00:00.0000000 2015-01-02 12:00:00.000 2015-01-02 15:00:00.000 06:00:00.0000000 2015-01-03 06:00:00.000 2015-01-03 09:00:00.000 09:00:00.0000000 2015-01-03 09:00:00.000 2015-01-03 12:00:00.000 12:00:00.0000000 2015-01-03 12:00:00.000 2015-01-03 15:00:00.000 06:00:00.0000000 2015-01-04 06:00:00.000 2015-01-04 09:00:00.000 09:00:00.0000000 2015-01-04 09:00:00.000 2015-01-04 12:00:00.000 12:00:00.0000000 2015-01-04 12:00:00.000 2015-01-04 15:00:00.000 06:00:00.0000000 2015-01-05 06:00:00.000 2015-01-05 09:00:00.000 09:00:00.0000000 2015-01-05 09:00:00.000 2015-01-05 12:00:00.000 12:00:00.0000000 2015-01-05 12:00:00.000 2015-01-05 15:00:00.000 |
Thanks for listening folks, and I hope that if you ever need to generate fixed duration time slots to support your application, you’ll find this article and embrace whichever technique suits your fancy.
Load comments