Calculate Number of working Days in a Given Period

[shilpareddy2787]

Hello,

I have some total values, I want to calculate percenatge of these Total Values.
I want to divide the total with No. Of working Days Excluding Saturdays and Sundays in a given period.

How to calculate the Total Number of working Days in a given period . Let us say If i give the period as 08/01/2008 to 08/15/2008, I want total number of working days as 11.

Please help me

[deepuv04]

Hi,
Try the following query:

SELECT Count(Date_Column)
FROM   Table_Name
WHERE  Date_Column BETWEEN '08/01/2008' AND '08/15/2008'
       AND Datename(weekday, Date_Column) NOT IN ('Sunday','Saturday')
 
 

Thanks

[shilpareddy2787]

I don't have any Date column.
I just want to calculate the Total No. Of Working Days in the Given Period

[deepuv04]

I don't have any Date column.
I just want to calculate the Total No. Of Working Days in the Given Period

Hi,
Try the following one

Declare @startdate datetime 
Declare @enddate datetime 
 
Set @startdate = '08/01/2008'
Set @enddate = '08/15/2008'
 
SELECT Count(Date)
FROM ( Select dateadd(dd,number,@startdate)  as Date
       from master.dbo.spt_values 
       where master.dbo.spt_values.type='p' AND dateadd(dd,number,@startdate)<=(@enddate)
      ) AS T
WHERE Datename(weekday, T.Date) NOT IN ('Sunday','Saturday')
 
 

Thanks

[bvandale]

I found this on another site:

select @weekDaysInPeriod = (
DATEDIFF(DAY, @StartDate, @EndDate) + 1
+ (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2
- (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2
- CASE DATEDIFF(DAY, '17530101', @StartDate) % 7
WHEN 6 THEN 1
ELSE 0
END
- CASE DATEDIFF(DAY, '17530101', @EndDate) % 7
WHEN 5 THEN 1
ELSE 0
END
)