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’)
–Verify the data
SELECT * FROM Test1
–Generate Monthly Report
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)
Big Data and SQL 
One comment