Counting Transactions per Hour using a Pivot Table
By Garth Wells
on 09 September 2001
| 4 Comments
| Tags: SELECT
One of the FAQs on the SQL Server newsgroups concerns creating a pivot table using T-SQL. A previous article, Dynamic Cross-Tabs/Pivot Tables, covered some more advanced ways to create a pivot table, but I want to show a simpler solution that I have used on one of my current projects.
When you create a pivot table you rotate rows to columns. This produces a resultset that facilitates trend analysis. In the example shown in this article I show how to use a pivot table to
display the number of transactions that occur per hour per day. Before we get to that, though, let's take a look at what the resultset looks like when a pivot table is not used.
The Sequential Approach
The following script creates a few transactions and shows a
sequential resultset.
SET NOCOUNT ON
CREATE TABLE Transactions
(
Tra_ID int IDENTITY NOT NULL PRIMARY KEY,
Tra_Date smalldatetime NOT NULL
)
go
INSERT Transactions VALUES ('8/1/01 08:00:00')
INSERT Transactions VALUES ('8/1/01 08:00:00')
INSERT Transactions VALUES ('8/1/01 13:00:00')
INSERT Transactions VALUES ('8/1/01 17:00:00')
INSERT Transactions VALUES ('8/2/01 09:00:00')
INSERT Transactions VALUES ('8/2/01 10:00:00')
INSERT Transactions VALUES ('8/2/01 16:00:00')
INSERT Transactions VALUES ('8/2/01 17:00:00')
INSERT Transactions VALUES ('8/3/01 15:00:00')
INSERT Transactions VALUES ('8/3/01 15:30:00')
INSERT Transactions VALUES ('8/4/01 11:00:00')
INSERT Transactions VALUES ('8/4/01 17:00:00')
go
SELECT CONVERT(varchar(8),Tra_Date,1) AS 'Day',
CASE WHEN DATEPART(hour,Tra_Date) = 7 THEN '7am-8'
WHEN DATEPART(hour,Tra_Date) = 8 THEN '8am-9'
WHEN DATEPART(hour,Tra_Date) = 9 THEN '9am-10'
WHEN DATEPART(hour,Tra_Date) = 10 THEN '10am-11'
WHEN DATEPART(hour,Tra_Date) = 11 THEN '11am-Noon'
WHEN DATEPART(hour,Tra_Date) = 12 THEN 'Noon-1'
WHEN DATEPART(hour,Tra_Date) = 13 THEN '1pm-2'
WHEN DATEPART(hour,Tra_Date) = 14 THEN '2pm-3'
WHEN DATEPART(hour,Tra_Date) = 15 THEN '3pm-4'
WHEN DATEPART(hour,Tra_Date) = 16 THEN '4pm-5'
WHEN DATEPART(hour,Tra_Date) = 17 THEN '5pm-6' END AS TranHour,
COUNT(*) AS TranCount
FROM Transactions
GROUP BY CONVERT(varchar(8),Tra_Date,1),DATEPART(hh,Tra_Date)
ORDER BY CONVERT(varchar(8),Tra_Date,1)
-- Results --
Day TranHour TranCount
-------- --------- -----------
08/01/01 8am-9 2
08/01/01 1pm-2 1
08/01/01 5pm-6 1
08/02/01 9am-10 1
08/02/01 10am-11 1
08/02/01 4pm-5 1
08/02/01 5pm-6 1
08/03/01 3pm-4 2
08/04/01 11am-Noon 1
08/04/01 5pm-6 1
The code certainly works, but you really have to pay attention
to tell when days change and it is difficult to see how the
transactions vary for the same hour across days.
Creating a Pivot Table
The pivot table approach creates a tabular format of the data and makes analysis much easier. The code that does this is shown here.
SELECT CONVERT(varchar(8),Tra_Date,1) AS 'Day',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 7 THEN 1 ELSE 0 END) AS '7am-8',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 8 THEN 1 ELSE 0 END) AS '8am-9',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 9 THEN 1 ELSE 0 END) AS '9am-10',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 10 THEN 1 ELSE 0 END) AS '10am-11',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 11 THEN 1 ELSE 0 END) AS '11am-Noon',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 12 THEN 1 ELSE 0 END) AS 'Noon-1',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 13 THEN 1 ELSE 0 END) AS '1pm-2',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 14 THEN 1 ELSE 0 END) AS '2pm-3',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 15 THEN 1 ELSE 0 END) AS '3pm-4',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 16 THEN 1 ELSE 0 END) AS '4pm-5',
SUM(CASE WHEN DATEPART(hour,Tra_Date) = 17 THEN 1 ELSE 0 END) AS '5pm-6'
FROM Transactions
GROUP BY CONVERT(varchar(8),Tra_Date,1)
ORDER BY CONVERT(varchar(8),Tra_Date,1)
-- Resultset --
Day 7am-8 8am-9 9am-10 10am-11 11am-Noon ...
-------- ----------- ----------- ----------- ----------- ---------
08/01/01 0 2 0 0 0 ...
08/02/01 0 0 1 1 0 ...
08/03/01 0 0 0 0 0 ...
08/04/01 0 0 0 0 1 ...
The key in implementing a pivot table is rotating the rows to
columns. This is accomplished with the CASE statement and SUM
aggregation function. When the CASE statement evaluates to true a
one is returned and when it evaluates to false a 0 is returned. The
SUM function adds all the values together to produce a column
value. This code can look a little confusing at first, but after
you experiment with it (break it and then fix it) for a few
minutes it will make sense.
Conclusion
Pivot tables are a great way to summarize data for analysis. And
if you happen to know how to use the DTS Export Wizard it is easy
to export the data returned to an Excel spreadsheet. I prefer
giving end-users data in Excel because they can massage it any
way they choose, which reduces the number of request to me.
Garth
www.SQLBook.com
