Counting Transactions per Hour using a Pivot Table

By Garth Wells on 9 September 2001 | 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