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 =

;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

Go

http://sqlserverbuddy.blogspot.com/2011/06/how-many-days-in-month.html

--Result

--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 posthttp://sqlserverbuddy.blogspot.com/2011/06/how-many-days-in-month.html

--Result

## No comments:

## Post a Comment