Case When Between issue

Question

Case When Between issue

jcobb 20350

Default port

Points: 1438
More actions
February 4, 2019 at 11:01 am

#373741

I have a list of suppliers and their next review date. The date is always on a Monday but there could be some outliers in that week. Right now I'm using the DW part of GETDATE and adding a value so that I can see all the suppliers where the review date is this week or in the past. (Some only come up once a month, so their last review may be 3 weeks back). Works great right now. What I need to see in another query are the supplier up next week but exclude the results of the first query.
The SELECT below is my current query and shows all dates from this week backward. I need something like a BETWEEN that will give me the two dates from 2-11-19, but exclude the 2-18-19 date and forward and anything from 2-4-19 backward.
CREATE TABLE jec_supplier_review (supplier varchar(5), review_date date)
INSERT INTO jec_supplier_review (supplier, review_date) VALUES ('12', '2-4-19'), ('20', '2-11-19'),('35', '2-11-19'), ('44', '2-18-19') ,('52', '12-17-18'),('81', '1-7-19'),('99', '1-14-19')
SELECT * FROM jec_supplier_review WHERE review_date <= CASE WHEN DATEPART(dw,GETDATE()) = 1 THEN GETDATE()+7 WHEN DATEPART(dw,GETDATE()) = 2 THEN GETDATE()+6 WHEN DATEPART(dw,GETDATE()) = 3 THEN GETDATE()+5 WHEN DATEPART(dw,GETDATE()) = 4 THEN GETDATE()+4 WHEN DATEPART(dw,GETDATE()) = 5 THEN GETDATE()+3 WHEN DATEPART(dw,GETDATE()) = 6 THEN GETDATE()+2 WHEN DATEPART(dw,GETDATE()) = 7 THEN GETDATE()+1 END ORDER BY review_date

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

you want to use Datetime for all values

here i am stripping out time to get the first day of the current week/Monday, and adding either one or two weeks.
look at how this works, i think this is what you are after:

CREATE TABLE jec_supplier_review (supplier varchar(5), review_date date)

INSERT INTO jec_supplier_review (supplier, review_date) VALUES ('12', '2-4-19'), ('20', '2-11-19'),('35', '2-11-19'), ('44', '2-18-19') ,('52', '12-17-18'),('81', '1-7-19'),('99', '1-14-19')

select DATEADD(ww, DATEDIFF(ww,0,getdate()), 0), --midnight monday of the current week, DATEADD(dd,7,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0)), --midnight monday of the next week DATEADD(dd,14,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0)), --midnight monday of the two weeks ahead DATEPART(dw,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0)), *

FROM jec_supplier_review WHERE review_date >=DATEADD(dd,7,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0)) AND review_date < DATEADD(dd,14,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

jcobb 20350 - Monday, February 4, 2019 11:01 AM
I have a list of suppliers and their next review date. The date is always on a Monday but there could be some outliers in that week. Right now I'm using the DW part of GETDATE and adding a value so that I can see all the suppliers where the review date is this week or in the past. (Some only come up once a month, so their last review may be 3 weeks back). Works great right now. What I need to see in another query are the supplier up next week but exclude the results of the first query.
The SELECT below is my current query and shows all dates from this week backward. I need something like a BETWEEN that will give me the two dates from 2-11-19, but exclude the 2-18-19 date and forward and anything from 2-4-19 backward.
CREATE TABLE jec_supplier_review (supplier varchar(5), review_date date)
INSERT INTO jec_supplier_review (supplier, review_date) VALUES ('12', '2-4-19'), ('20', '2-11-19'),('35', '2-11-19'), ('44', '2-18-19') ,('52', '12-17-18'),('81', '1-7-19'),('99', '1-14-19')
SELECT * FROM jec_supplier_review WHERE review_date <= CASE WHEN DATEPART(dw,GETDATE()) = 1 THEN GETDATE()+7 WHEN DATEPART(dw,GETDATE()) = 2 THEN GETDATE()+6 WHEN DATEPART(dw,GETDATE()) = 3 THEN GETDATE()+5 WHEN DATEPART(dw,GETDATE()) = 4 THEN GETDATE()+4 WHEN DATEPART(dw,GETDATE()) = 5 THEN GETDATE()+3 WHEN DATEPART(dw,GETDATE()) = 6 THEN GETDATE()+2 WHEN DATEPART(dw,GETDATE()) = 7 THEN GETDATE()+1 END ORDER BY review_date

It seems that your CASE expression can be simplified to something like this:
SELECT * FROM jec_supplier_review WHERE review_date <= GETDATE() + 8 - DATEPART(dw,GETDATE()) ORDER BY review_date;
To solve your other problem, just changing the numbers could work.
SELECT * FROM jec_supplier_review WHERE review_date > GETDATE() + 8 - DATEPART(dw,GETDATE()) AND review_date <= GETDATE() + 15 - DATEPART(dw,GETDATE()) ORDER BY review_date;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jcobb 20350 Default port Points: 1438 More actions · Answer 3

Lowell - Monday, February 4, 2019 11:14 AM
you want to use Datetime for all values
here i am stripping out time to get the first day of the current week/Monday, and adding either one or two weeks.
look at how this works, i think this is what you are after:
CREATE TABLE jec_supplier_review (supplier varchar(5), review_date date)
INSERT INTO jec_supplier_review (supplier, review_date) VALUES ('12', '2-4-19'), ('20', '2-11-19'),('35', '2-11-19'), ('44', '2-18-19') ,('52', '12-17-18'),('81', '1-7-19'),('99', '1-14-19')
select DATEADD(ww, DATEDIFF(ww,0,getdate()), 0), --midnight monday of the current week, DATEADD(dd,7,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0)), --midnight monday of the next week DATEADD(dd,14,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0)), --midnight monday of the two weeks ahead DATEPART(dw,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0)), *
FROM jec_supplier_review WHERE review_date >=DATEADD(dd,7,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0)) AND review_date < DATEADD(dd,14,DATEADD(ww, DATEDIFF(ww,0,getdate()), 0))

Thank you Lowell. I see how you are using the DATEADD along with the DATDIFF to get the additional days. My actual table is datetime, but I was trying to keep it simple for the data I provided. Thanks again.

jcobb 20350 Default port Points: 1438 More actions · Answer 4

Luis Cazares - Monday, February 4, 2019 11:19 AM
jcobb 20350 - Monday, February 4, 2019 11:01 AM
I have a list of suppliers and their next review date. The date is always on a Monday but there could be some outliers in that week. Right now I'm using the DW part of GETDATE and adding a value so that I can see all the suppliers where the review date is this week or in the past. (Some only come up once a month, so their last review may be 3 weeks back). Works great right now. What I need to see in another query are the supplier up next week but exclude the results of the first query.
The SELECT below is my current query and shows all dates from this week backward. I need something like a BETWEEN that will give me the two dates from 2-11-19, but exclude the 2-18-19 date and forward and anything from 2-4-19 backward.
CREATE TABLE jec_supplier_review (supplier varchar(5), review_date date)
INSERT INTO jec_supplier_review (supplier, review_date) VALUES ('12', '2-4-19'), ('20', '2-11-19'),('35', '2-11-19'), ('44', '2-18-19') ,('52', '12-17-18'),('81', '1-7-19'),('99', '1-14-19')
SELECT * FROM jec_supplier_review WHERE review_date <= CASE WHEN DATEPART(dw,GETDATE()) = 1 THEN GETDATE()+7 WHEN DATEPART(dw,GETDATE()) = 2 THEN GETDATE()+6 WHEN DATEPART(dw,GETDATE()) = 3 THEN GETDATE()+5 WHEN DATEPART(dw,GETDATE()) = 4 THEN GETDATE()+4 WHEN DATEPART(dw,GETDATE()) = 5 THEN GETDATE()+3 WHEN DATEPART(dw,GETDATE()) = 6 THEN GETDATE()+2 WHEN DATEPART(dw,GETDATE()) = 7 THEN GETDATE()+1 END ORDER BY review_date
It seems that your CASE expression can be simplified to something like this:
SELECT * FROM jec_supplier_review WHERE review_date <= GETDATE() + 8 - DATEPART(dw,GETDATE()) ORDER BY review_date;
To solve your other problem, just changing the numbers could work.
SELECT * FROM jec_supplier_review WHERE review_date > GETDATE() + 8 - DATEPART(dw,GETDATE()) AND review_date <= GETDATE() + 15 - DATEPART(dw,GETDATE()) ORDER BY review_date;

Thanks Luis. Your solution is a little different than the one from Lowell, but works just as well. It's great to have two perspectives towards the final outcome. The cleaner look on the first query will really help also. Thanks again.