Author |
Topic |
mattie
Starting Member
13 Posts |
Posted - 2012-12-14 : 12:57:38
|
I have a field called Sched_Date that I use in a Case Statement: Case When datepart (weekday, SCHED_DATE) = 1 Then 'Sunday' When datepart (weekday, SCHED_DATE) = 7 Then 'Saturday' End as WeekendI want to be able to only include the sched_Date for the upcoming Weekend in the Where Clause, and include both Saturday and Sunday. I would like it to run automatically, so I don't have to rewrite the dates each time. Is this possible? Thx |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-14 : 13:16:26
|
You can do the following. Check the boundary conditions though - like, if today is a Sunday, do you want to consider the following Saturday as a weekend or not etc.SELECT CASE WHEN CASE WHEN DATEPART(weekday, c) = 1 THEN 'Sunday' WHEN DATEPART(weekday, SCHED_DATE) = 7 THEN 'Saturday' END AND DATEDIFF(dd, GETDATE(), SCHED_DATE) > 6 THEN 1 ELSE 0 END AS Weekend A bit shorter code that is also language independent is as follows:SELECT CASE WHEN DATEDIFF(dd, 0, SCHED_DATE)%7 > 4 AND DATEDIFF(dd, GETDATE(), SCHED_DATE) > 6 THEN 1 ELSE 0 END AS Weekend |
|
|
mattie
Starting Member
13 Posts |
Posted - 2012-12-14 : 13:39:42
|
Yes, when the query is run on Saturday or Sunday, I would like it to query the CURRENT Weekend. When it's any other day, the following weekend. Is there a way to do this in the Where Clause without the CASE? I keep getting syntax errors when I try this. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-14 : 13:55:53
|
I am not seeing any syntax error in the code that I posted. Can you post the code that you are seeing the error in and also the error message? |
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-12-14 : 13:56:53
|
SELECT MAX(SCHED_DATE) ,DATEPART(weekday,SCHED_DATE) FROM TABLENAME WHERE DATEPART(weekday,SCHED_DATE) = 7 OR DATEPART(weekday,SCHED_DATE) = 1 GROUP BY DATEPART(weekday,SCHED_DATE) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-14 : 14:07:30
|
quote: Originally posted by shilpash SELECT MAX(SCHED_DATE) ,DATEPART(weekday,SCHED_DATE) FROM TABLENAME WHERE DATEPART(weekday,SCHED_DATE) = 7 OR DATEPART(weekday,SCHED_DATE) = 1 GROUP BY DATEPART(weekday,SCHED_DATE)
I am not seeing any syntax error in this code. Can you post the error message you are getting? |
|
|
mattie
Starting Member
13 Posts |
Posted - 2012-12-14 : 14:43:33
|
The last post seemed to work, but it shows all the weekends in December, instead of just the following weekend, i.e. 12/15 and 12/17. Thx! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-14 : 14:47:36
|
quote: Originally posted by mattie The last post seemed to work, but it shows all the weekends in December, instead of just the following weekend, i.e. 12/15 and 12/17. Thx!
True, it would do that - but you did not include the additional condition in the where clause that I had suggested. Add that as well. |
|
|
mattie
Starting Member
13 Posts |
Posted - 2012-12-14 : 15:01:48
|
Can you tell me what I'm missing in the following? Thx for your help. Select MAX(Sched_Date) , datepart (weekday, SCHED_DATE) as Day_Week From Table where (DATEPART(weekday,SCHED_DATE) = 7OR DATEPART(weekday,SCHED_DATE) = 1) GROUP BY DATEPART(weekday,SCHED_DATE) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-14 : 15:07:48
|
[code]SELECT MAX(Sched_Date), DATEPART(weekday, SCHED_DATE) AS Day_WeekFROM tblWHERE ( ( DATEPART(weekday, SCHED_DATE) = 7 OR DATEPART(weekday, SCHED_DATE) = 1 ) AND DATEDIFF(dd, GETDATE(), SCHED_DATE) > 6 )GROUP BY DATEPART(weekday, SCHED_DATE)[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-14 : 15:14:01
|
for just getting following weekend this is enoughSELECT MAX(SCHED_DATE),DATEPART(weekday,SCHED_DATE)FROM TABLENAMEWHERE SCHED_DATE >=DATEADD(day,(DATEDIFF(day,0,GETDATE())/7) * 7,5)AND SCHED_DATE < DATEADD(day,(DATEDIFF(day,0,GETDATE())/7) * 7,7)GROUP BY DATEPART(weekday,SCHED_DATE) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
mattie
Starting Member
13 Posts |
Posted - 2012-12-14 : 16:35:04
|
This worked great. Reading article also now. Thx to both of you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-15 : 12:18:58
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|