how do i find a specific status, and only that status

Question

how do i find a specific status, and only that status

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

saravanatn - Saturday, July 14, 2018 12:57 AM
Jeff Moden - Friday, July 13, 2018 9:37 PM
saravanatn - Tuesday, July 10, 2018 9:18 AM
jeffshelix - Tuesday, July 10, 2018 8:52 AM
We have a table that contains part numbers, along with a status.
because of the way the DB was designed, a single part, let's say ABC123, can have more that one status. (The Part Number is not the key field).
So i might see in Example 1:
1. ABC123, status OPEN
2. ABC123, status CLOSED.
Example 2
1. ABC456 status Open
i need to search the table for Parts that have an OPEN status, and only and open status.
So i want to find ABC456, but not ABC123.
I am not sure how to code that.

I guess this is what you want
SELECT PART_NUMBER,STATUS FROM TABLE_NAME WHERE STATUS='OPEN' AND PART_NUMBER NOT IN (SELECT PART_NUMBER FROM TABLE_NAME WHERE PART_NUMBER='ABC123')
Unfortunately, that presupposes knowledge of the parts that have more than 1 status and hardcodes them.
Yes Jeff you are right. But OP doesn't provide sufficient I think.. I am not complaining because when I post questions previously in this forum I also doesn't provide sufficient information or proper test data . Now I am improved in providing information in forum and hope this OP also does the same in future.
With that myself or some other will be able to provide great solution.

I think that the OP provided just exactly what was needed for his understanding of the problem (which I believe is in error). He simply stated that he wanted to return only those products that have only the status of "OPEN" across multiple rows and no other statuses. He then gave a simple example with only two products rather than listing dozens or hundreds of products. You have to understand that his two product example was a greatly reduced example for simplification of what was required for many more rows. There was no "mind reading" required here. The requirements are incredibly simple and John Mitchell quickly sussed the problem for not only the simple example, but for the larger problem, as well.

And, no... neither this nor my previous post was meant to chastise. It was meant to be a suggestion towards how to read and approach such problems. It's along the same lines as "Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Again and as explained below, I believe the OP may actually be asking for the wrong thing based on his interpretation of the data he sees v.s. what could actually happen in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 2

Lynn Pettis - Tuesday, July 10, 2018 9:36 AM
John Mitchell-245523 - Tuesday, July 10, 2018 9:26 AM
Lynn Pettis - Tuesday, July 10, 2018 9:21 AM
why test both MIN and MAX to be the same?
Because Open and Closed might not be the only two statuses.
John
In that case, the OP needs to provide more details. Another one, could the same part have the same STATUS multiple times, such as OPEN multiple times, CLOSED multiple times, some other status multiple times.

I have to seriously agree with this. Did the OP actually mean "Find only those part numbers that CURRENTLY have an OPEN status" regardless of the number of rows that have a status and has also made the dangerous assumption that the status rows will not contain more than a single cycle of Open/Closed statuses (for example)? If so, a "status date" or some other temporal indicator as a column in the table would be necessary.

And one cannot assume that a table will only have one cycle of Open/Closed (for example) because, like I said, it's a dangerous non-future-proof assumption that is likely not enforced. Future requirements may allow for such a thing and it's better to bullet-proof the code up front rather than a customer or other user finding out otherwise at crunch time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Lynn Pettis - Tuesday, July 10, 2018 12:40 PM
So am I wrong to provide other alternatives? Is so I will just go away.

No... you're not wrong. It may be that the OP assumes that there will be only one OPEN status and has not future-proofed for the possibility of there being multiple Open/Closed cycles and, like I said above, may actually be looking for the much more broad definition of "Find only those products that have a CURRENT status of OPEN" and simply didn't know how to state it in his request.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)