A lot of people use calendar tables. I’ve blogged about it before. They’re incredibly helpful. Now, have you ever needed a table of minutes?
You’re probably asking, “Jeremiah, why the heck would I ever need a table of minutes?” Well, dear reader, I’m going to tell you that. Please stop interrupting.
Let’s say you have a report. This report shows the sum of sales per 5 minute increment. You could do a lot of trickery with math to make this report happen, doing things like this to get the 5 minute interval:
SELECT
(DATEPART(mi, CAST('2009-01-01 00:01:00' as datetime))+4) / 5 * 5 AS [05]
It works, but it’s ugly as sin. I said to myself, “Self, there has to be a better way.” Turns out that there is a better way: table valued functions!
I created a table valued function to return 60 rows, one for each minute. It also rounds to the nearest 5, 10, 15, and 30 minute intervals. This makes it possible to change the reporting interval very easily by using a join. Check it out:
SELECT DATEPART(hh, s.SaleTime) AS TheHour,
mt.Five AS NearestFive,
SUM(s.SalesAmount) AS AmountSold
FROM dbo.Sales AS s
INNER JOIN dbo.MinutesTable() AS mt ON DATEPART(mi, s.SaleTime) = mt.[Minute]
WHERE -- something is true
GROUP BY DATEPART(hh, s.SaleTime), mt.Five
Why do I call this the lazy way to do this? Because now that I’ve written it once I never have to do it again. I can add new columns to dbo.MinutesTable() without having to worry about breaking anything or copying code incorrectly from one query to another.
Here’s the code to create the function:
CREATE FUNCTION dbo.MinutesTable ()
RETURNS @minutes TABLE (
[Minute] TINYINT,
Five TINYINT,
Ten TINYINT,
Fifteen TINYINT,
Thirty TINYINT
)
AS BEGIN
INSERT INTO @minutes VALUES (0,60,60,60,60)
(1,5,10,15,30),
(2,5,10,15,30),
/* you get the gist of it */
(59,60,60,60,60);
RETURN ;
END
Update: Thanks to Brad Schulz this has gone for a single post about my laziness to an example of a refactoring you can make. If you ever see a table being generated like this, you can take a look at it and determine how you can change it into an inline select. The query optimizer is going to do something completely different for each plan. With the inline select, SQL Server is able to determine that there are 60 rows in our table and can build a much more efficient execution plan.
CREATE FUNCTION [dbo].[MinutesTable] ()
RETURNS TABLE
AS
RETURN
SELECT 0 AS [Minute], 60 AS Five, 50 AS Ten, 60 AS Fifteen, 60 AS Thirty
UNION ALL
SELECT 1, 5, 10, 15, 30
UNION ALL
/* ... this still makes sense, right? ... */
UNION ALL
SELECT 59, 60, 60, 60, 60 ;
GO
Comments
Nice!
But wouldn’t it perhaps be better to make the function an inline TVF?
CREATE FUNCTION dbo.MinutesTable()
RETURNS TABLE
AS
RETURN
SELECT [Minute],Five,Ten,Fifteen,Thirty
FROM (VALUES (1,5,10,15,30), … , (60,60,60,60,60)) X([Minute],Five,Ten,Fifteen,Thirty)
It creates INTs instead of TINYINTs, but that’s probably okay.
–Brad
I’m not sure that it would matter either way. It would be a fun benchmark to see. If it does matter, I suspect the performance difference is probably negligible. David Stein mentioned using a persisted table on disk. If I weren’t so lazy, I probably would have created a minutes table and been done with it, but I got tired of writing regular expressions to manipulate the data, so I wrote a blog post about it instead
Thanks for the comment. I may have to try a benchmark tomorrow during some kind of “ETL is running, I’m refusing to do work” moment.
The inline is almost always much faster… I did a quick benchmark using your function and did a query against the Sales.SalesOrderHeader table in AdventureWorks (using ShipDate and TotalDue in a query just like the one you posted).
The multi-line function took about 3-4 seconds to run.
The inline function was instantaneous… just because it could “macro-ize” or expand the function directly into the query.
By the way… the function doesn’t have an entry for 0 (I had to add one to make it work with a datetime field with a midnight time)… and I suppose 60 shouldn’t be in the list of values either.
Oooh, good point, I hadn’t thought that the optimizer would automagic the inline select into a table. It makes sense.
I think that, tomorrow, I’ll push this into a table to alleviate any performance penalty at all.
Thank, as always, for your contributions.
Very nice. Now if I can download the data from my bicycle computer, speed per minute, I could build a table and use this function to chart how slow I’m going. Probably better not to see.
No matter how slow you’re going, I’m sitting at home
Maybe I’m just lazier than thou, but I’m not sure how you’re using this effectively as written? Shouldn’t you assign the interval rounding down, not up? i.e. SELECT 0 AS [Minute], 0 AS Five, 0 AS Ten, 0 AS Fifteen, 0 AS Thirty UNION ALL … SELECT 59 AS [Minute], 55 AS Five, 50 AS Ten, 45 AS Fifteen, 30 AS Thirty?
Thought it worked fine until I tried to construct fake times using this, and I ended up with 08:60, which for some dumb reason nobody recognizes as a valid time. My watch says it’s 00:800 AM, and that’s fine with me, not sure why the users are complaining…
But maybe I’m just not using it in the same manner?
Edumacate me, oh great one….
Well, we have a report that goes :05, :10, … :55, :60 across the top and this was the best way to achieve that. This should probably be a magical function or table with a RoundTo00 and a RoundTo60 column or something equally silly like that. I should probably add some words up at the top to clarify that…
I’ll recognize 8:60 as a valid time: means I’m 60 minutes late for work
I’ve sometimes constructed huge tables of dates for data warehousing projects – similar situation.
How right you are! Calendar tables are a godsend. I’ve blogged about them before, but never about how I actually use them. Maybe I should do that one of these days…