By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,838 Members | 2,222 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
4 Replies


deepuv04
Expert 100+
P: 227
Hi,
Try the following query:

Expand|Select|Wrap|Line Numbers
  1. SELECT Count(Date_Column)
  2. FROM   Table_Name
  3. WHERE  Date_Column BETWEEN '08/01/2008' AND '08/15/2008'
  4.        AND Datename(weekday, Date_Column) NOT IN ('Sunday','Saturday')
  5.  
  6.  

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

deepuv04
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
  1. Declare @startdate datetime 
  2. Declare @enddate datetime 
  3.  
  4. Set @startdate = '08/01/2008'
  5. Set @enddate = '08/15/2008'
  6.  
  7. SELECT Count(Date)
  8. FROM ( Select dateadd(dd,number,@startdate)  as Date
  9.        from master.dbo.spt_values 
  10.        where master.dbo.spt_values.type='p' AND dateadd(dd,number,@startdate)<=(@enddate)
  11.       ) AS T
  12. WHERE Datename(weekday, T.Date) NOT IN ('Sunday','Saturday')
  13.  
  14.  
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

Post your reply

Sign in to post your reply or Sign up for a free account.