I need an SQL function to get data grouped by month.
I have in the column CPCCodeID
codes (ex: 512456). I need to count and group for the first two digits. I need this separated for months (!!!) and to make it easier I did this loop that goes through every month and groups it the way I need it.
At this point I came up with this solution:
USE CCILCDatabase;
GO
DECLARE @mes int;
SET @mes = 1;
WHILE (select @mes) <= 12
BEGIN
SELECT LEFT(CONVERT(VARCHAR(10),[CPCCodeID]),2), COUNT(*)
FROM PublicContestCPC
where YEAR(DateCreated) = '2017' AND MONTH(DateCreated) = (select @mes)
GROUP BY LEFT(CONVERT(VARCHAR(10),[CPCCodeID]),2)
ORDER BY LEFT(CONVERT(VARCHAR(10),[CPCCodeID]),2)
SET @mes += 1;
END
PRINT @mes;
I know you can change this code to not use loop
, but my SQL knowledge is somewhat limited. I wanted to use this to export to an Excel sheet, but the way it is implemented does not work.