r270ba
asked on
SQL Last Weekday Statement
Attached below is my sql query that is working fine. I would like to add a WHERE clause to show
WHERE a.docdate = 'last weekday'
If today is Monday then I would want it to show all records where a.docdate='Friday'
Any ideas?
WHERE a.docdate = 'last weekday'
If today is Monday then I would want it to show all records where a.docdate='Friday'
Any ideas?
select a.sopnumbe, a.custname, a.cstponbr, a.docdate, a.user2ent, b.ponumber from
(select * from sop10100 where docid = 'WARRANTY') A
left join
(select * from sop60100) B
on a.sopnumbe = b.sopnumbe
group by a.sopnumbe, a.custname, b.ponumber, a.docdate, a.cstponbr, a.user2ent
ASKER
Sorry...I was using Friday as an example. What I am looking for is the previous weekday. So, if today is Tuesday it would look for Monday's date. If today is Monday it would look for Friday's date.
ASKER
Today is 06/24/08 and I would want a.docdate='06/23/08'
Monday was 06/23/08 so I would want a.docdate='06/20/08' (Friday)
Monday was 06/23/08 so I would want a.docdate='06/20/08' (Friday)
OK...try this:
select a.sopnumbe, a.custname, a.cstponbr, a.docdate, a.user2ent, b.ponumber from
(select * from sop10100 where docid = 'WARRANTY') A
left join
(select * from sop60100) B
on a.sopnumbe = b.sopnumbe
where convert(varchar(10), a.docdate , 101) = convert(varchar(10), case when datename(dw, getdate()) = 'Monday' THEN DATEADD(d, -3, getdate()) else dateadd(d, -1, getdate()), 101)
group by a.sopnumbe, a.custname, b.ponumber, a.docdate, a.cstponbr, a.user2ent
select a.sopnumbe, a.custname, a.cstponbr, a.docdate, a.user2ent, b.ponumber from
(select * from sop10100 where docid = 'WARRANTY') A
left join
(select * from sop60100) B
on a.sopnumbe = b.sopnumbe
where convert(varchar(10), a.docdate , 101) = convert(varchar(10), case when datename(dw, getdate()) = 'Monday' THEN DATEADD(d, -3, getdate()) else dateadd(d, -1, getdate()), 101)
group by a.sopnumbe, a.custname, b.ponumber, a.docdate, a.cstponbr, a.user2ent
ASKER
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.
I have check the () and they appear to be correct. Any ideas?
Incorrect syntax near ','.
I have check the () and they appear to be correct. Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Chapmandew...as always...thanks for the expert help!
ASKER
I am sorry but I did not realize that ScottPletcher had posted and I selected the last post as the answer thinking it was Chapmandew. Can I please award the points correctly?
please do. :)
ASKER
I need a mod to reset this post and let me re award the points. Thanks and sorry!
ASKER
I have reqeuested for this to be re-opened.
ASKER
Thanks for both of your help. I gave Chapmandew 300 points because I used his solution but I feel like ScottPletcher actually had a more "defensive" solution by adding the if Sunday clause (even though I only thought about Monday). I appreciate both of your help!
select a.sopnumbe, a.custname, a.cstponbr, a.docdate, a.user2ent, b.ponumber from
(select * from sop10100 where docid = 'WARRANTY') A
left join
(select * from sop60100) B
on a.sopnumbe = b.sopnumbe
where datename(dw, a.docdate) = 'Friday'
group by a.sopnumbe, a.custname, b.ponumber, a.docdate, a.cstponbr, a.user2ent