SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Datediff to exclude weekends
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sonjan
Starting Member

Australia
22 Posts

Posted - 02/05/2013 :  18:03:34  Show Profile  Reply with Quote
Is it possible to calculate difference in days between dates that exclude weekends, then calculate 8 hour day by hourly rate eg

start date: 28/06/2012 end date: 25/07/2012. Actual difference in days is 27, but remove the weekends (8 days) = 19 days. The actual duration in hours between start and end dates = 648 but should be between 7.00am - 3.00pm (8 hours). Hourly rate = $5.00.

End result should be: 19 days x 8 hours = 152 hours x $5.00 = $760.00

Any help would be much appreciated. Thanks

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 02/05/2013 :  18:57:36  Show Profile  Reply with Quote
5.00*8*( DATEDIFF(dd,'20120628','20120725')-2*DATEDIFF(wk,'20120628','20120725') )
Go to Top of Page

sonjan
Starting Member

Australia
22 Posts

Posted - 02/12/2013 :  22:20:51  Show Profile  Reply with Quote
Thanks James - this is great,much appreciated! Apology for delay.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/13/2013 :  04:48:47  Show Profile  Reply with Quote
see

http://visakhm.blogspot.in/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000