I would use this:
SELECT Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0),
Category,
COUNT(Status) TotalCount
FROM MyTable
WHERE Closing_Date >= ‘2012-02-01’
AND Closing_Date <= '2012-12-31'
AND Defect_Status1 IS NOT NULL
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category;
This will group by the first of every month, so
`DATEADD(MONTH, DATEDIFF(MONTH, 0, '20130128'), 0)`
will give '20130101'. I generally prefer this method as it keeps dates as dates.
Alternatively you could use something like this:
SELECT Closing_Year = DATEPART(YEAR, Closing_Date),
Closing_Month = DATEPART(MONTH, Closing_Date),
Category,
COUNT(Status) TotalCount
FROM MyTable
WHERE Closing_Date >= ‘2012-02-01’
AND Closing_Date <= '2012-12-31'
AND Defect_Status1 IS NOT NULL
GROUP BY DATEPART(YEAR, Closing_Date), DATEPART(MONTH, Closing_Date), Category;
It really depends what your desired output is. (Closing Year is not necessary in your example, but if the date range crosses a year boundary it may be).
Use the DATEPART function to extract the month from the date.
So you would do something like this:
SELECT DATEPART(month, Closing_Date) AS Closing_Month, COUNT(Status) AS TotalCount
FROM t
GROUP BY DATEPART(month, Closing_Date)