grouping by quarterSELECTDATEPART(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_NoFROM dbo.RequestGROUP BY DATEPART(QQ, ReceivedDate), YEAR(ReceivedDate)ORDER BY YEAR(ReceivedDate), DATEPART(QQ, ReceivedDate)
grouping by yearSELECT YEAR(ReceivedDate) [Year],count(Test_No) Test_NoFROM dbo.RequestGROUP BY YEAR(ReceivedDate)ORDER BY YEAR(ReceivedDate)
grouping by all side by sideSELECT 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_NoFROM dbo.RequestGROUP BY GROUPING SETS ((MONTH(ReceivedDate)), (DATEPART(QQ, ReceivedDate)), (YEAR(ReceivedDate)))ORDER BY YEAR(ReceivedDate), DATEPART(QQ, ReceivedDate), MONTH(ReceivedDate)
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/