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
