{"id":1987,"date":"2015-04-22T00:00:00","date_gmt":"2015-04-22T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/time-slots-an-essential-extension-to-calendar-tables\/"},"modified":"2021-09-29T16:21:28","modified_gmt":"2021-09-29T16:21:28","slug":"time-slots-an-essential-extension-to-calendar-tables","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/time-slots-an-essential-extension-to-calendar-tables\/","title":{"rendered":"Time Slots – An Essential Extension to Calendar Tables"},"content":{"rendered":"
\n

\tOftentimes you’ll need to generate a table of time slots to use in much the same way as a Calendar table<\/a>. 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. <\/p>\n

“Generate them when you need them.”
\t— SQL MVP Jeff Moden<\/a> on time slots <\/p>\n

\tIndeed, since we’re all probably familiar with Tally tables<\/a>, whether in a physical table or one that is generated in-line, this is relatively easy to accomplish. <\/p>\n

Using Time-slots: A Real World Example <\/h2>\n

\tSuppose 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. <\/p>\n

-- A table holding our widget-producing machines\nCREATE TABLE dbo.Machines\n(\n    MachineID       SMALLINT IDENTITY PRIMARY KEY\n    ,MachineName    VARCHAR(30)\n);\nGO\n\n-- Sample data for the three widget-producing machines\nINSERT INTO dbo.Machines (MachineName)\nVALUES('Widget Maker 1'),('Widget Maker 2'),('Widget Maker 3');\nGO\n\n-- A table holding our production results\nCREATE TABLE dbo.MachineProduction\n(\n    MachineID       SMALLINT\n    ,TimeProduced   DATETIME\n    ,QtyOfWidgets   INT\n    ,PRIMARY KEY (MachineID, TimeProduced)\n);\nGO\n\n-- Sample data for production results\nWITH ProductionDays (pd) AS\n(\n    -- Three days worth\n    SELECT '2015-04-01' \n    UNION ALL SELECT '2015-04-02'\n    UNION ALL SELECT '2015-04-03'\n),\n    Tally (n) AS\n(\n    -- A Tally table that produces 1,000 rows\n    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))\n    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)               \n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)\n)\nINSERT INTO dbo.MachineProduction\n(\n    MachineID, TimeProduced, QtyOfWidgets\n)\nSELECT MachineID, TimeProduced\n    -- Random number of widgets produced (between 1 and 20)\n    ,1+ABS(CHECKSUM(NEWID()))%20\nFROM\n(\n    -- Use DISTINCT here to avoid PRIMARY KEY duplicates\n    SELECT DISTINCT MachineID\n        -- We'll add to our production date (pd) a random number of seconds between\n        -- 1 and 86,400 (number of seconds in a day) to get a discrete time of day, \n        -- then create a Cartesian product against this and our production machines\n        ,TimeProduced=DATEADD(second, ABS(CHECKSUM(NEWID()))%86400, pd)\n    FROM ProductionDays a\n    CROSS APPLY Tally b\n    CROSS APPLY (VALUES(1),(2),(3)) c (MachineID)\n) a\n-- Create some gaps where the machines were down (not producing anything) on those days\nWHERE NOT ((MachineID = 1 AND DATEADD(day, DATEDIFF(day, 0, TimeProduced), 0) = '2015-04-02') OR \n    (MachineID = 2 AND DATEADD(day, DATEDIFF(day, 0, TimeProduced), 0) = '2015-04-01'));\n<\/pre>\n
 \tNote 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. <\/p>\n
 \tNow 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. <\/p>\n
WITH TimeSlotsOffsets \n    (TimeSlot, StartOffsetHrs, EndOffsetHrs)\nAS\n(\n    -- Six hours at a time\n    SELECT      CAST('00:00' AS TIME), 0, 6\n    UNION ALL SELECT '06:00', 6, 12\n    UNION ALL SELECT '12:00', 12, 18\n    UNION ALL SELECT '18:00', 18, 24\n),\n    ProductionDays (pd) AS\n(\n    -- Three days worth\n    SELECT '2015-04-01' \n    UNION ALL SELECT '2015-04-02'\n    UNION ALL SELECT '2015-04-03'\n)\nSELECT TimeSlot     = [BaseDateWithTimeSlotStart]\n    ,a.MachineID\n    ,QtyofWidgets   = ISNULL(b.QtyOfWidgets, 0)\nFROM\n(\n    -- Create Cartesian product\n    SELECT c.MachineID\n        ,[BaseDate] = a.pd\n        ,[BaseDateWithTimeSlotStart]    = DATEADD(hour, b.StartOffsetHrs, a.pd)\n        ,[BaseDateWithTimeSlotEnd]      = DATEADD(hour, b.EndOffsetHrs, a.pd)\n    -- Production days of interest\n    FROM ProductionDays a\n    -- Time slots of 6 hours\n    CROSS JOIN TimeSlotsOffsets b\n    -- Unique MachineIDs\n    CROSS JOIN dbo.Machines c\n) a\nOUTER APPLY \n(\n    -- Sum the widgets produced\n    SELECT b.MachineID, QtyOfWidgets=SUM(b.QtyOfWidgets)\n    FROM dbo.MachineProduction b\n    WHERE a.MachineID = b.MachineID AND\n        -- where production time is within the time slot period\n        b.TimeProduced >= a.[BaseDateWithTimeSlotStart] AND \n            b.TimeProduced < a.[BaseDateWithTimeSlotEnd] \n    GROUP BY b.MachineID\n) b\nORDER BY MachineID, [BaseDateWithTimeSlotStart];<\/pre>\n
 \tA 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. <\/p>\n
 \tLet’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<\/a> (CTE<\/a>). 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. <\/p>\n
DECLARE @TimeSlotHrsDuration    SMALLINT = 6;     -- 6 hours\n\nWITH Tally (n) AS\n(\n    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1      -- Zero based\n    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n)\n), \n    TimeSlotsOffsets (StartOffsetHrs, EndOffsetHrs)\nAS\n(\n    -- Varying duration for each time slot\n    SELECT n*@TimeSlotHrsDuration, (1+n)*@TimeSlotHrsDuration     \n    FROM Tally\n    WHERE n < 24 \/ @TimeSlotHrsDuration\n),\n    ProductionDays (pd) AS\n(\n    -- Three days worth\n    SELECT '2015-04-01' \n    UNION ALL SELECT '2015-04-02'\n    UNION ALL SELECT '2015-04-03'\n)\nSELECT TimeSlot     = [BaseDateWithTimeSlotStart]\n    ,a.MachineID\n    ,QtyofWidgets   = ISNULL(b.QtyOfWidgets, 0)\n    ,DailyQty       = ISNULL(SUM(b.QtyOfWidgets) OVER \n        (\n            PARTITION BY a.MachineID, [BaseDate]\n            ORDER BY [BaseDateWithTimeSlotStart]\n            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n        ), 0)\nFROM\n(\n    -- Create Cartesian product\n    SELECT c.MachineID\n        ,[BaseDate] = a.pd\n        ,[BaseDateWithTimeSlotStart]    = DATEADD(hour, b.StartOffsetHrs, a.pd)\n        ,[BaseDateWithTimeSlotEnd]      = DATEADD(hour, b.EndOffsetHrs, a.pd)\n    -- Production days of interest\n    FROM ProductionDays a\n    -- Time slots of 6 hours\n    CROSS JOIN TimeSlotsOffsets b\n    -- Unique MachineIDs\n    CROSS JOIN dbo.Machines c\n) a\nOUTER APPLY \n(\n    -- Sum the widgets produced\n    SELECT b.MachineID, QtyOfWidgets=SUM(b.QtyOfWidgets)\n    FROM dbo.MachineProduction b\n    WHERE a.MachineID = b.MachineID AND\n        -- where production time is within the time slot period\n        b.TimeProduced >= a.[BaseDateWithTimeSlotStart] AND \n            b.TimeProduced < a.[BaseDateWithTimeSlotEnd] \n    GROUP BY b.MachineID\n) b\nORDER BY MachineID, [BaseDateWithTimeSlotStart];<\/pre>\n
 \tIf 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. <\/p>\n
TimeSlot                 MachineID  QtyofWidgets  DailyQty\n2015-04-01 00:00:00.000  1          2226          2226\n2015-04-01 06:00:00.000  1          2955          5181\n2015-04-01 12:00:00.000  1          2548          7729\n2015-04-01 18:00:00.000  1          2622          10351\n2015-04-02 00:00:00.000  1          0             0\n2015-04-02 06:00:00.000  1          0             0\n2015-04-02 12:00:00.000  1          0             0\n2015-04-02 18:00:00.000  1          0             0\n2015-04-03 00:00:00.000  1          2753          2753\n2015-04-03 06:00:00.000  1          2795          5548\n2015-04-03 12:00:00.000  1          2555          8103\n2015-04-03 18:00:00.000  1          2639          10742\n2015-04-01 00:00:00.000  2          0             0\n2015-04-01 06:00:00.000  2          0             0\n2015-04-01 12:00:00.000  2          0             0\n2015-04-01 18:00:00.000  2          0             0\n2015-04-02 00:00:00.000  2          2499          2499\n2015-04-02 06:00:00.000  2          2608          5107\n2015-04-02 12:00:00.000  2          2500          7607\n2015-04-02 18:00:00.000  2          2741          10348\n2015-04-03 00:00:00.000  2          2550          2550\n2015-04-03 06:00:00.000  2          2528          5078\n2015-04-03 12:00:00.000  2          2563          7641\n2015-04-03 18:00:00.000  2          2676          10317\n2015-04-01 00:00:00.000  3          2537          2537\n2015-04-01 06:00:00.000  3          2627          5164\n2015-04-01 12:00:00.000  3          2508          7672\n2015-04-01 18:00:00.000  3          2611          10283\n2015-04-02 00:00:00.000  3          2752          2752\n2015-04-02 06:00:00.000  3          2614          5366\n2015-04-02 12:00:00.000  3          2778          8144\n2015-04-02 18:00:00.000  3          2528          10672\n2015-04-03 00:00:00.000  3          2500          2500\n2015-04-03 06:00:00.000  3          2427          4927\n2015-04-03 12:00:00.000  3          2665          7592\n2015-04-03 18:00:00.000  3          2599          10191<\/pre>\n
 \tThis query generates a total of 36 rows (3 days x 3 machines x 4 time slots per day). <\/p>\n
 \tIn 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<\/code>, which will be introduced just a bit later. <\/p>\n
An In-line Tally Table for Generating Time Slots<\/h2>\n
 \tTo generate time slots of any duration, even down to the second, we can use an in-line Tally table such as the one below. <\/p>\n
WITH Tally (n) AS\n(\n    SELECT \n        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based\n    -- Returns exactly 86400 rows (number of seconds in a day)\n    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows \n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows\n)                                                                           -- = 86,400 rows\nSELECT n\nFROM Tally;<\/pre>\n
 \tOur 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. <\/p>\n
Time Slots – Hours or Minutes in a Day<\/h2>\n
 \tBy adding a TOP clause to our Tally table along with a little DATEADD<\/a> 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. <\/p>\n
WITH Tally (n) AS\n(\n    SELECT TOP (24)\n        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based\n    -- Returns exactly 86400 rows (number of seconds in a day)\n    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows \n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows\n)                                                                           -- = 86,400 rows\n-- 24 time slots, one for each hour of the day\nSELECT TimeSlot=DATEADD(hour, n, CAST('00:00' AS TIME))\nFROM Tally;\n\nWITH Tally (n) AS\n(\n    SELECT TOP (3600)\n        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based\n    -- Returns exactly 86400 rows (number of seconds in a day)\n    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows \n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows\n)                                                                           -- = 86,400 rows\n-- 3600 time slots, one for each minute of the day\nSELECT TimeSlot=DATEADD(minute, n, CAST('00:00' AS TIME))\nFROM Tally;<\/pre>\n
 \tNotice 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. <\/p>\n
 \tWe 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. <\/p>\n
WITH Tally (n) AS\n(\n    SELECT TOP (16)\n        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based\n    -- Returns exactly 86400 rows (number of seconds in a day)\n    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows \n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows\n)                                                                           -- = 86,400 rows\n-- 16 time slots starting at 05:00, one for each hour in two 16 hour shifts\nSELECT TimeSlot=DATEADD(hour, n, CAST('05:00' AS TIME))\nFROM Tally;<\/pre>\n
Creating Time Slots across Multiple Days<\/h2>\n
 \tIf 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<\/a> (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. <\/p>\n
WITH Tally (n) AS\n(\n    SELECT TOP (24)\n        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based\n    -- Returns exactly 86400 rows (number of seconds in a day)\n    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows \n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows\n)                                                                           -- = 86,400 rows\n-- 24 time slots, one for each hour of the day\nSELECT TimeSlot=DATEADD(hour, DATEDIFF(hour, 0, DATEADD(hour, n, CAST('00:00' AS TIME))), [Date])\nFROM dbo.GenerateCalendar('2015-04-01', 2) a\nCROSS APPLY Tally b;\n<\/pre>\n
 \tMy GenerateCalendar<\/code> FUNCTION returns [Date] (actually a DATETIME<\/a>) as one of its columns. It is necessary to convert the time slot to a time offset in hours using DATEDIFF<\/a>, 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). <\/p>\n
TimeSlot\n2015-04-01 00:00:00.000\n2015-04-01 01:00:00.000\n2015-04-01 02:00:00.000\n-- 18 rows redacted\n2015-04-01 21:00:00.000\n2015-04-01 22:00:00.000\n2015-04-01 23:00:00.000\n2015-04-02 00:00:00.000\n2015-04-02 01:00:00.000\n2015-04-02 02:00:00.000\n-- 18 rows redacted\n2015-04-02 21:00:00.000\n2015-04-02 22:00:00.000\n2015-04-02 23:00:00.000\n<\/pre>\n
Another Example: Fifteen Minute Time Slots<\/h2>\n
 \tSuppose 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). <\/p>\n
WITH Tally (n) AS\n(\n    SELECT TOP (96)\n        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based\n    -- Returns exactly 86400 rows (number of seconds in a day)\n    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows \n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows\n)                                                                           -- = 86,400 rows\n-- 3600 time slots, one for each minute of the day\nSELECT TimeSlot=DATEADD(minute, n*15, CAST('00:00' AS TIME))\nFROM Tally;<\/pre>\n
 \tWe’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. <\/p>\n
TimeSlot\n00:00:00.0000000\n00:15:00.0000000\n00:30:00.0000000\n00:45:00.0000000\n01:00:00.0000000\n01:15:00.0000000\n... 84 rows redacted  \n22:30:00.0000000\n22:45:00.0000000\n23:00:00.0000000\n23:15:00.0000000\n23:30:00.0000000\n23:45:00.0000000\n<\/pre>\n
Clock Arithmetic is for Geeks, so let’s just Do It Once<\/h2>\n
 \tI 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. <\/p>\n
 \tPerhaps 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. <\/p>\n
CREATE FUNCTION [dbo].[CalculateTimeSlots]\n-- A function to calculate time slots over a twenty-four hour period\n (\n    -- @SlotType: 'hour', 'minute' or 'second' (invalid defaults to 'hour')\n    -- Other allowed abbreviations as in DATEADD\/DATEDIFF are also supported\n    -- for these three slot types.\n    @SlotType           VARCHAR(6)   \n    -- @SlotDuration: Must be a zero remainder divisor of @SlotType\n    --  For @SlotType='second' then when 60%@SlotDuration = 0 the increment is valid  \n    --  For @SlotType='minute' then when 60%@SlotDuration = 0 the increment is valid  \n    --  For @SlotType='hour' then when 24%@SlotDuration = 0 the increment is valid\n    --  If invalid, defaults to 1  \n    ,@SlotDuration      SMALLINT \n    ,@BaseDate          DATETIME \n    ,@NumberOfDays      INT      \n) RETURNS TABLE WITH SCHEMABINDING\nRETURN\nWITH Tally (n) AS\n(\n    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1                   -- zero-based\n    -- Returns exactly 86400 rows (number of seconds in a day)\n    FROM       (VALUES(0),(0),(0),(0),(0),(0))                         a(n) -- 6 rows \n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n) -- x12 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         d(n) -- x10 rows\n    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))         e(n) -- x10 rows\n),                                                                          -- = 86,400 rows\n    MagicNumbersForHMS AS\n(\n    -- All clock arithmetic magic numbers are consolidated here to drive the calculations\n    -- that will follow.\n    SELECT SlotType = 'second', SlotOffset=1, UnitsPerNext=60, UnitsPerDay=86400\n    UNION ALL SELECT  'minute',            60,             60,             1440\n    UNION ALL SELECT  'hour',              3600,           24,             24\n),\n    MiscParameters (t, SlotOffset, SlotDuration, TimeSlots, BaseDate, NoDays) AS\n(\n    -- Start with the base offset as a TIME datatype\n    SELECT CAST('00:00' AS TIME)\n        ,b.SlotOffset\n        -- Set SlotDuration to 1 if not evenly divisible by specified @SlotDuration\n        ,CASE UnitsPerNext % @SlotDuration \n              WHEN 0 \n              THEN @SlotDuration \n              ELSE 1 \n              END\n        -- Calculate the number of time slots for this call\n        ,CASE\n              WHEN @SlotDuration <= 0 OR UnitsPerNext%@SlotDuration <> 0\n              THEN UnitsPerDay\n              ELSE UnitsPerDay \/ ISNULL(@SlotDuration, 1)\n              END\n        -- Pass the BaseDate forward\n        ,@BaseDate\n        -- Calculate the number of days (1 if not specified) for the Cartesian product\n        ,CASE WHEN @BaseDate IS NULL OR NULLIF(@NumberOfDays, 0) IS NULL\n              THEN 1 \n              ELSE ABS(@NumberOfDays) \n              END\n    FROM \n    (\n        -- Default the @SlotType to hours if invalid\n        SELECT SlotType=CASE \n              -- Valid for hours\n              WHEN @SlotType IN ('hour', 'hh') \n              THEN 'hour' \n              -- Valid for minutes\n              WHEN @SlotType IN ('minute', 'mi', 'n') \n              THEN 'minute' \n              -- Valid for seconds\n              WHEN @SlotType IN ('second','ss', 's') \n              THEN 'second' \n              ELSE 'hour' \n              END\n    ) a\n    JOIN MagicNumbersForHMS b ON a.SlotType = b.SlotType\n)\nSELECT [TimeSlot]                   = DATEADD(second, c.n * a.SlotOffset * a.SlotDuration, t) \n    -- Offset based on @SlotType\n    ,[TimeSlotOffsetUnits]          = c.n * a.SlotDuration \n    -- Offset based on seconds\n    ,[TimeSlotOffsetSeconds]        = c.n * a.SlotOffset * SlotDuration\n    -- Extended return columns starting with base date\n    ,[BaseDate]                     = CAST(b.[Date] AS DATE)\n    -- Base date plus offset\n    ,[BaseDateWithTimeSlotStart]    = DATEADD(second, c.n*a.SlotOffset*a.SlotDuration, b.[Date])\n    -- Base date plus offset plus duration\n    ,[BaseDateWithTimeSlotEnd]      = DATEADD(second, (c.n+1)*a.SlotOffset*a.SlotDuration, b.[Date])\nFROM MiscParameters a\n-- CROSS APPLY to generate the requisite number of days from the extended parameters\n-- (1 day\/row if not specified)\nCROSS APPLY \n(\n    SELECT TOP (a.NoDays) [Date]=b.n + a.BaseDate \n    FROM Tally b\n    ORDER BY n \n) b\n-- Generate the Cartesian product of days vs. time slots\nCROSS APPLY \n(\n    SELECT TOP (a.TimeSlots) c.n\n    FROM Tally c\n    ORDER BY n\n) c;<\/pre>\n
 \tWe’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. <\/p>\n
 \tLet’s describe each of the arguments, including the last two which I call “extended arguments” because of the additional value they add. <\/p>\n