[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Last Weekday Statement

Posted on 2008-06-24
15
Medium Priority
?
738 Views
Last Modified: 2010-07-27
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

0
Comment
Question by:r270ba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
15 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21856770
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
 

Author Comment

by:r270ba
ID: 21856867
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
 

Author Comment

by:r270ba
ID: 21856879
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:chapmandew
ID: 21856908
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
 

Author Comment

by:r270ba
ID: 21856972
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

I have check the () and they appear to be correct.  Any ideas?
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 1200 total points
ID: 21857028
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 21857145
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
 

Author Comment

by:r270ba
ID: 21857907
Chapmandew...as always...thanks for the expert help!
0
 

Author Comment

by:r270ba
ID: 21857918
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21857942
please do.  :)
0
 

Author Comment

by:r270ba
ID: 21858026
I need a mod to reset this post and let me re award the points.  Thanks and sorry!
0
 

Author Comment

by:r270ba
ID: 21858964
I have reqeuested for this to be re-opened.
0
 

Author Closing Comment

by:r270ba
ID: 31470191
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question