428,838 Members | 2,222 Online
Need help? Post your question and get tips & solutions from a community of 428,838 IT Pros & Developers. It's quick & easy.

# Calculate Number of working Days in a Given Period

 P: 11 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 Aug 11 '08 #1
4 Replies

 Expert 100+ P: 227 Hi, Try the following query: Expand|Select|Wrap|Line Numbers 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 Aug 11 '08 #2

 P: 11 I don't have any Date column. I just want to calculate the Total No. Of Working Days in the Given Period Aug 11 '08 #3

 Expert 100+ P: 227 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 Expand|Select|Wrap|Line Numbers 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 Aug 11 '08 #4

 P: 1 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 ) Apr 20 '12 #5