Friday, January 29, 2010

SQL Server - Hourly Report - Group by Hour

Sometimes it requires to know how many inserts are happend every hour in a transactions table OR how many payments made every hour OR to retrieve the hourly based report, whatever term it is, the requirement is to generate a report based on hours from a datetime column. If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Follow the examples.
CREATE TABLE Test1(
AccountID INT,
AccountNumber VARCHAR(20),
PaidAmount DECIMAL,
PaidDate SMALLDATETIME
)

INSERT INTO Test1 VALUES(221001, 'A1020101', 2000.00, '2010-01-20 13:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020102', 3300.00, '2010-01-20 13:33:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020103', 1100.00, '2010-01-20 15:01:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020104', 5400.00, '2010-01-20 15:49:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020105', 5200.00, '2010-01-20 16:30:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020106', 3400.00, '2010-01-20 16:59:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020107', 1200.00, '2010-01-20 17:10:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020108', 4200.00, '2010-01-20 18:22:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020109', 2100.00, '2010-01-20 18:43:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020110', 3400.00, '2010-01-20 19:12:00:000')
INSERT INTO Test1 VALUES(221002, 'A1020111', 5500.00, '2010-01-20 22:19:00:000')

SELECT * FROM Test1

SELECT
CONVERT(VARCHAR(10), PaidDate, 101) Day,
CONVERT(VARCHAR(2), PaidDate, 108) Hour,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
CONVERT(VARCHAR(10), PaidDate, 101),
CONVERT(VARCHAR(2), PaidDate, 108)

No comments:

Post a Comment

Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...