Finding weekends in a time interval 1

[TMRO]

Hi guys,

anybody knows a function to calculate how many saturdays or any other day are in a time interval?

I need to find out the number of working days in a variable time interval.

Thank you,
TMRO

 

[gmmastros]

Do you have a numbers table in your database. If you don't, I suggest you add a permanent numbers table. You can do all sorts of things with it. In the example I show below, I create a temp table called #Numbers. If you want to make a permanent table, remove the # sign from the table name.

Create Table #Numbers (Num Integer Identity(0,1))

Declare @i Integer
Set @i = 0

While @i < 10000
	Begin
		insert Into #Numbers Default Values
		Set @i = @i + 1
	End

Declare @StartDate DateTime
Declare @EndDate DateTime

Set @StartDate = '2006-06-01'
Set @EndDate = '2006-07-01'

Select Num, DateName(weekday, Num+@StartDate),
       @StartDate + Num,
       Sum(case DateName(weekday, Num + @StartDate) 
           When 'Saturday' Then 1
           When 'Sunday' Then 1
           Else 0 End) As WeekendCount
From   #Numbers
Where  (Num + @StartDate) Between @StartDate And @EndDate
Group By Num

Select Sum(case DateName(weekday, Num + @StartDate) 
           When 'Saturday' Then 1
           When 'Sunday' Then 1
           Else 0 End) As WeekendCount
From   #Numbers
Where  (Num + @StartDate) Between @StartDate And @EndDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TMRO]

Thanks a lot gmmastros.
Great coding , great help.