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?
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

Open in new window

r270baAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
sorry about that..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())end), 101)
group by a.sopnumbe, a.custname, b.ponumber, a.docdate, a.cstponbr, a.user2ent
0
 
chapmandewCommented:
something like this?  You just want it where the date is Friday?

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
0
 
r270baAuthor Commented:
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.  
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
r270baAuthor Commented:
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)
0
 
chapmandewCommented:
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
0
 
r270baAuthor Commented:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

I have check the () and they appear to be correct.  Any ideas?
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
select a.sopnumbe, a.custname, a.cstponbr, a.docdate, a.user2ent, b.ponumber from
(select * from sop10100 where docid = 'WARRANTY' and
    docdate >= dateadd(day, - case datename(weekday, getdate()) when 'monday' then 3 when 'sunday' then 2 else 1 end, convert(char(8), getdate(), 112)) and
    docdate < dateadd(day, - case datename(weekday, getdate()) when 'monday' then 2 when 'sunday' then 1 else 0 end, convert(char(8), getdate(), 112))
) 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
0
 
r270baAuthor Commented:
Chapmandew...as always...thanks for the expert help!
0
 
r270baAuthor Commented:
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?
0
 
chapmandewCommented:
please do.  :)
0
 
r270baAuthor Commented:
I need a mod to reset this post and let me re award the points.  Thanks and sorry!
0
 
r270baAuthor Commented:
I have reqeuested for this to be re-opened.
0
 
r270baAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.