August 21, 2017 at 6:55 am
Hi Everyone,
I have a query that brings back a list of orders. I have the SELECT portion to where i want it, but the WHERE clause is giving me fits.
Here is the WHERE clause:
WHERE ORDERS.Status IN ('New','Open')
AND FSLog.IDNum IN (16,19,116,136,225,345)
AND FSLog.CompletedDate IS NULL
The FSLog tracks the status of an order and the IDNum is a particular type of status. For this instance, I am looking at '345' which lets say means "Documents Delivered". There is also another status I need to add which is '333' and let's call this "Documents Received".
What I need is to modify the WHERE clause to look for Documents received with a completed date, but documents delivered completed date to be null.
I've tried every possible combination I can think of and every time I run it I get no recrods back.
This clause I've tried and no records return, for example.
WHERE ORDERS.Status IN ('New','Open')
AND (FSLog.IDNum IN (16,19,116,136,225) AND FSLog.CompletedDate IS NULL )
AND ((FSLog.IDNum IN (345) AND FSLog.CompletedDate IS NULL) OR (FSLog.IDNum IN(333) AND FSLog.CompletedDate IS NOT NULL))
I've even tried cross applies, additional joins with aliases, and nothing seems to work.
Thanks for your help in advance!
August 21, 2017 at 7:13 am
Like this, maybe?WHERE ORDERS.Status IN ('New','Open')
AND ((FSLog.IDNum IN (16,19,116,136,225,345) AND FSLog.CompletedDate IS NULL)
OR (FSLog.IDNum = 333 AND FSLog.CompletedDate IS NOT NULL))
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 21, 2017 at 7:15 am
WHERE ORDERS.Status IN ('New','Open')
AND ((FSLog.IDNum IN (16,19,116,136,225) AND FSLog.CompletedDate IS NULL)
OR ((FSLog.IDNum IN (345) AND FSLog.CompletedDate IS NULL) OR (FSLog.IDNum IN(333) AND FSLog.CompletedDate IS NOT NULL)))
John
August 21, 2017 at 7:16 am
Hi
You should change your second AND statement as well to check for the three different conditions.
This should work if I understand the question correctly:
WHERE ORDERS.Status IN ( 'New', 'Open' )
AND (
FSLog.IDNum IN ( 16, 19, 116, 136, 225 )
AND FSLog.CompletedDate IS NULL
)
OR (
FSLog.IDNum IN ( 345 )
AND FSLog.CompletedDate IS NULL
)
OR (
FSLog.IDNum IN ( 333 )
AND FSLog.CompletedDate IS NOT NULL
)
August 21, 2017 at 7:19 am
Phil Parkin - Monday, August 21, 2017 7:13 AMLike this, maybe?WHERE ORDERS.Status IN ('New','Open')AND ((FSLog.IDNum IN (16,19,116,136,225,345) AND FSLog.CompletedDate IS NULL)
OR (FSLog.IDNum = 333 AND FSLog.CompletedDate IS NOT NULL))
Well, I do get results but not what Im looking for.
I have to take the 345 code out and check it with the 333 code. the 345 code has to be null and the 333 code has to have a date in it. all of the other codes still have to be null.
August 21, 2017 at 7:23 am
meichmann - Monday, August 21, 2017 7:19 AMPhil Parkin - Monday, August 21, 2017 7:13 AMLike this, maybe?WHERE ORDERS.Status IN ('New','Open')AND ((FSLog.IDNum IN (16,19,116,136,225,345) AND FSLog.CompletedDate IS NULL)
OR (FSLog.IDNum = 333 AND FSLog.CompletedDate IS NOT NULL))
Well, I do get results but not what Im looking for.
I have to take the 345 code out and check it with the 333 code. the 345 code has to be null and the 333 code has to have a date in it. all of the other codes still have to be null.
My code does that already. At least, I think it does. Can you post an example (Status, IDNum, CompletedDate) combo which fails the test?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 21, 2017 at 7:23 am
Looking at your WHERE clause your query must be joining the two tables which would result in the following possible combinations
New 16 2017-08-01
New 16 NULL
Open 16 NULL
Open 16 2017-08-01
New 19 2017-08-01
New 19 NULL
Open 19 NULL
Open 19 2017-08-01
New 116 2017-08-01
New 116 NULL
Open 116 NULL
Open 116 2017-08-01
New 136 2017-08-01
New 136 NULL
Open 136 NULL
Open 136 2017-08-01
New 225 2017-08-01
New 225 NULL
Open 225 NULL
Open 225 2017-08-01
New 345 2017-08-01
New 345 NULL
Open 345 NULL
Open 345 2017-08-01
use a GROUP BY and SUM, i.e.
GROUP BY Document
HAVING SUM(CASE
WHEN FSLog.IDNum = 345 AND FSLog.CompletedDate IS NULL THEN 1
WHEN FSLog.IDNum = 333 AND FSLog.CompletedDate IS NOT NULL THEN 1
WHEN FSLog.IDNum IN (16,19,116,136,225) AND FSLog.CompletedDate IS NULL THEN 1
ELSE 0 END) = 7
Far away is close at hand in the images of elsewhere.
Anon.
August 21, 2017 at 7:24 am
I think you're losing logical consistency with your AND / OR & braces. Try this:
WHERE ORDERS.Status IN ('New','Open')
AND (
(FSLog.IDNum IN (333) AND FSLog.CompletedDate IS NOT NULL)
OR
(FSLog.IDNum IN (16,19,116,136,225,345) AND FSLog.CompletedDate IS NULL)
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 21, 2017 at 7:25 am
After re-reading, I am wondering whether you are trying to combine multiple source rows into a single output row?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 21, 2017 at 7:30 am
it looks like your WHERE clause contradicts itself, the first combination of IDNum and CompletedDate also needs to be inside the parenthesis with the OR conditions, so maybe something like:WHERE ORDERS.Status IN ('New','Open')
AND ( (FSLog.IDNum IN (16,19,116,136,225) AND FSLog.CompletedDate IS NULL)
OR (FSLog.IDNum IN (345) AND FSLog.CompletedDate IS NULL)
OR (FSLog.IDNum IN (333) AND FSLog.CompletedDate IS NOT NULL)
)
EDIT: looks like I'm late to the game, I swear all these responses weren't there a minute ago
August 21, 2017 at 7:37 am
Chris Harshman - Monday, August 21, 2017 7:30 AMit looks like your WHERE clause contradicts itself, the first combination of IDNum and CompletedDate also needs to be inside the parenthesis with the OR conditions, so maybe something like:WHERE ORDERS.Status IN ('New','Open')
AND ( (FSLog.IDNum IN (16,19,116,136,225) AND FSLog.CompletedDate IS NULL)
OR (FSLog.IDNum IN (345) AND FSLog.CompletedDate IS NULL)
OR (FSLog.IDNum IN (333) AND FSLog.CompletedDate IS NOT NULL)
)
EDIT: looks like I'm late to the game, I swear all these responses weren't there a minute ago
Haha that's what I saw too 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 21, 2017 at 7:56 am
I will try all of these and let you know. thank you for all the responses!
August 21, 2017 at 9:33 am
Ok, so we came to the conclusion that we're going to leave that criteria out and make a separate query for just the 333 and 345 codes.
thank you everyone for your time and input!
🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply