Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nikhilkumardas209
Starting Member
3 Posts |
 Posted - 2013-07-17 : 03:45:54
|
| 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
52326 Posts |
Posted - 2013-07-17 : 04:26:50
|
| [code]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())[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
nikhilkumardas209
Starting Member
3 Posts |
Posted - 2013-07-17 : 07:12:02
|
| 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') |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-17 : 07:16:23
|
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 detailshttp://visakhm.blogspot.com/2012/08/creating-server-independent-day.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
nikhilkumardas209
Starting Member
3 Posts |
Posted - 2013-07-18 : 08:44:02
|
| Sir, I coudn't get your following functionality in above solution of your's.DATEDIFF(dd,0,date_created)% 7 > 4 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-18 : 09:32:47
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
To calculate 15 bussiness days