grouping by quarter
SELECT
DATEPART(QQ,ReceivedDate) [Quarter]
,YEAR(ReceivedDate) [Year]
,Case DATEPART(QQ,ReceivedDate)
When 1 Then 'I Quarter'
When 2 Then 'II Quarter'
When 3 Then 'III Quarter'
When 4 Then 'IV Quarter'
End [QuarterName]
,count(Test_No) Test_No
FROM dbo.Request
GROUP BY DATEPART(QQ, ReceivedDate), YEAR(ReceivedDate)
ORDER BY YEAR(ReceivedDate), DATEPART(QQ, ReceivedDate)
grouping by year
SELECT
YEAR(ReceivedDate) [Year]
,count(Test_No) Test_No
FROM dbo.Request
GROUP BY YEAR(ReceivedDate)
ORDER BY YEAR(ReceivedDate)
grouping by all side by side
SELECT
MONTH(ReceivedDate) [Month]
,DATEPART(QQ,ReceivedDate) [Quarter]
,YEAR(ReceivedDate) [Year]
,Case DATEPART(QQ,ReceivedDate)
When 1 Then 'I Quarter'
When 2 Then 'II Quarter'
When 3 Then 'III Quarter'
When 4 Then 'IV Quarter'
End [QuarterName]
,Case Month(ReceivedDate)
When 1 Then 'January'
When 2 Then 'February'
When 3 Then 'March'
When 4 Then 'April'
When 5 Then 'May'
When 6 Then 'June'
When 7 Then 'July'
When 8 Then 'August'
When 9 Then 'September'
When 10 Then 'October'
When 11 Then 'November'
When 12 Then 'December'
End [MonthName]
,count(Test_No) Test_No
FROM dbo.Request
GROUP BY GROUPING SETS ((MONTH(ReceivedDate)), (DATEPART(QQ, ReceivedDate)), (YEAR(ReceivedDate)))
ORDER BY YEAR(ReceivedDate), DATEPART(QQ, ReceivedDate), MONTH(ReceivedDate)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/