Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 total per quarter and year

Author  Topic 

eugz
Posting Yak Master

210 Posts

Posted - 2012-10-08 : 18:47:44
Hi All.

Here I count request per month

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 MONTH(ReceivedDate), DATEPART(QQ, ReceivedDate), YEAR(ReceivedDate)
ORDER BY YEAR(ReceivedDate), DATEPART(QQ, ReceivedDate), MONTH(ReceivedDate)


How to calculate request per quarter and year?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-08 : 19:41:05
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/

Go to Top of Page
   

- Advertisement -