SQL Server - Monthly Report - Group by Month

The following example guide you to retrieve a monthly report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a monthly report by GROUPING DATE column.

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
DATENAME(mm, PaidDate) Month,
YEAR(PaidDate) Year,
COUNT(*) iCount,
SUM(PaidAmount) Amount
FROM Test1
GROUP BY
DATENAME(mm, PaidDate),
Year(PaidDate),
Month(PaidDate)
ORDER BY Month(PaidDate)