I need to get counts by quarters and by years and I’m having a serious brain fart this afternoon. Below is my results so far and it’s not working (my brain nor the query). What I need is the count of records having a COMPLETION_DATE broken down by quarters and by year. Something like below, but it does not have to be identical. As long as it get the point across.
Year Quarters Total
2014 q1 = 200
q2 = 100
q3 = 100
q4 = 100 500
By the way, when I run this query in 2008r2 I receive the following error:
Invalid column name 'quarter'.
WHEN MONTH(a.COMPLETION_DATE) BETWEEN 1 AND 3 THEN 'Q1'
WHEN MONTH(a.COMPLETION_DATE) BETWEEN 4 AND 6 THEN 'Q2'
WHEN MONTH(a.COMPLETION_DATE) BETWEEN 7 AND 9 THEN 'Q3'
WHEN MONTH(a.COMPLETION_DATE) BETWEEN 10 AND 12 THEN 'Q4'
END AS [quarter],
YEAR(a.COMPLETION_DATE) AS [year],
FROM dbo.History a
ID = '5192714'