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)
 To calculate 15 bussiness days
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nikhilkumardas209
Starting Member

India
3 Posts

Posted - 07/17/2013 :  03:45:54  Show Profile  Reply with Quote
I have a requirement to write a SQL query to calculate 15 business days from today's date excluding Weekends.

Suppose the query is:- SELECT * FROM your_table WHERE ((DATEPART(dw, date_created) + @ @DATEFIRST ) % 7) NOT IN (0, 1)

But the problem is i have to make query according to months like if today is 15/07/2013, 15 business days before would be 24/06/13. (excluding 3 weekends).But some months may have even more weekends in between it.So the query is somewhat imperfect in which in some months it may return data for 13 days, some for 17 days and so on..

How to make that query dynamically ?

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/17/2013 :  04:26:50  Show Profile  Reply with Quote

SELECT * FROM your_table WHERE date_created = DATEADD(dd,-15-(DATEDIFF(wk,date_created,GETDATE())*2)- CASE WHEN DATEDIFF(dd,0,date_created)% 7 > 4 THEN 1 ELSE 0 END,GETDATE())


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nikhilkumardas209
Starting Member

India
3 Posts

Posted - 07/17/2013 :  07:12:02  Show Profile  Reply with Quote
SELECT * FROM your_table WHERE CAST(date_created AS DATE) <= CAST(DATEADD(dd,-15-(DATEDIFF(wk,GETDATE()-15,GETDATE())*2 + DATEDIFF(wk,GETDATE()-15 - DATEDIFF(wk,GETDATE()-15,GETDATE()),GETDATE())),GETDATE()) AS DATE)AND DATENAME(DW,date_created) NOT IN('Saturday','Sunday')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/17/2013 :  07:16:23  Show Profile  Reply with Quote
quote:
Originally posted by nikhilkumardas209

SELECT * FROM your_table WHERE CAST(date_created AS DATE) <= CAST(DATEADD(dd,-15-(DATEDIFF(wk,GETDATE()-15,GETDATE())*2 + DATEDIFF(wk,GETDATE()-15 - DATEDIFF(wk,GETDATE()-15,GETDATE()),GETDATE())),GETDATE()) AS DATE)AND DATENAME(DW,date_created) NOT IN('Saturday','Sunday')


This code is dependent on your sql servers language settings so it may break if you try to migrate it to server with different settings values.

See below link for more details

http://visakhm.blogspot.com/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nikhilkumardas209
Starting Member

India
3 Posts

Posted - 07/18/2013 :  08:44:02  Show Profile  Reply with Quote
Sir, I coudn't get your following functionality in above solution of your's.

DATEDIFF(dd,0,date_created)% 7 > 4
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  09:32:47  Show Profile  Reply with Quote
quote:
Originally posted by nikhilkumardas209

Sir, I coudn't get your following functionality in above solution of your's.

DATEDIFF(dd,0,date_created)% 7 > 4


read the posted link. It has the complete explanation.
% 7 will help us to find day of week starting with Monday = 0 to Sunday = 6 . So > 4 will ensure only Saturday and Sunday ie weekends

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.06 seconds. Powered By: Snitz Forums 2000