Weekday(s) statistics in a Month and Year

To findout count statistics for each weekdays in a particular Year and Month

--Creating a Stored Procedure
If OBJECT_ID('USP_Weekdays') Is Not Null
Drop Proc USP_Weekdays
Go
Create Proc USP_Weekdays
(
@Year Int,
@Month Varchar(15)
) As
Begin
Set Nocount on
 Declare @Days TinyInt
Select @Days = Dbo.HowManyDays(@Year,@Month)

;With WeekDays
As
(
Select 1 [Days]
Union All
Select [Days] + 1 from WeekDays where [Days] <31
)

Select SUM([Sunday]) [Sunday], SUM([Monday]) [Monday], Sum([Tuesday]) [Tuesday], Sum([Wednesday]) [Wednesday], SUM([Thursday]) [Thursday], Sum([Friday]) [Friday], SUM([Saturday]) [Saturday]
From
(
Select Case [Day] When 'Sunday' Then 1 Else 0 End [Sunday],
Case [Day] When 'Monday' Then 1 Else 0 End [Monday],
Case [Day] When 'Tuesday' Then 1 Else 0 End [Tuesday],
Case [Day] When 'Wednesday' Then 1 Else 0 End [Wednesday],
Case [Day] When 'Thursday' Then 1 Else 0 End [Thursday],
Case [Day] When 'Friday' Then 1 Else 0 End [Friday],
Case [Day] When 'Saturday' Then 1 Else 0 End [Saturday]
from
(
select DATENAME(weekday,Cast(@Year as Varchar) + '-' + @Month + '-' + Cast([Days] as varchar)) [Day] from WeekDays Where [Days] <= @Days
) as X
) as WeekDays
End
Go

--Executing the Stored Procedure
Exec USP_Weekdays 2011,'June'
Go

Note: You can find the dbo.HowManyDays function script from the following post
http://sqlserverbuddy.blogspot.com/2011/06/how-many-days-in-month.html

--Result

No comments:

Post a Comment