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


Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

SQL select Top 10 records for unique combination of two columns (13h)

SSRS Report Sorting with Grouping Issue (1d)

ORA-01476: divisor is equal to zero (1d)

Create new columns based on min and max values of a record with multiple rows (1d)

Memory Required for Reporting Services 2022 (1d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

sUMMING MULTIPLE COLUMNS (3d)

- Advertisement -