July 10, 2018 at 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.
July 10, 2018 at 9:15 am
SELECT
PartNo
, MAX(Status) AS MaxStatus
, MIN(Status) AS MinStatus
FROM Parts
GROUP BY PartNo
HAVING MaxStatus = 'Open'
AND MinStatus = 'Open'';
John
July 10, 2018 at 9:18 am
jeffshelix - Tuesday, July 10, 2018 8:52 AMWe 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 Openi 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')
Saravanan
July 10, 2018 at 9:19 am
thanks John!
July 10, 2018 at 9:21 am
John Mitchell-245523 - Tuesday, July 10, 2018 9:15 AMSELECT
PartNo
, MAX(Status) AS MaxStatus
, MIN(Status) AS MinStatus
FROM Parts
GROUP BY PartNo
HAVING MaxStatus = 'Open'
AND MinStatus = 'Open'';John
Here are two examples (and why test both MIN and MAX to be the same?):
DECLARE @MinimalInventoryExample TABLE (
MIEId INT NOT NULL IDENTITY(1,1)
, PartNumber VARCHAR(16) NOT NULL -- Define as appropriate in application, this is just a sample
, PartStatus VARCHAR(8) NOT NULL -- Define as appropriate in application, this is just a sample
);
INSERT INTO @MinimalInventoryExample([PartNumber],[PartStatus])
VALUES ('ABC123','OPEN'),('ABC123','CLOSED'),('ABC456','OPEN');
SELECT * FROM @MinimalInventoryExample AS [mie];
SELECT
[mie].[PartNumber]
, MIN([mie].[PartStatus])
FROM
@MinimalInventoryExample AS [mie]
GROUP BY
[mie].[PartNumber]
HAVING
MIN([mie].[PartStatus]) = 'OPEN';
-- OR
WITH BaseData AS (
SELECT
[mie].[PartNumber]
, [mie].[PartStatus]
, [rn] = ROW_NUMBER() OVER (PARTITION BY [mie].[PartNumber] ORDER BY [mie].[PartStatus] ASC)
FROM
@MinimalInventoryExample AS [mie]
)
SELECT
[bd].[PartNumber]
, [bd].[PartStatus]
FROM
[BaseData] AS [bd]
WHERE
[bd].[rn] = 1
AND [bd].[PartStatus] = 'OPEN';
GO
July 10, 2018 at 9:26 am
Lynn Pettis - Tuesday, July 10, 2018 9:21 AMwhy test both MIN and MAX to be the same?
Because Open and Closed might not be the only two statuses.
John
July 10, 2018 at 9:34 am
Lynn Pettis - Tuesday, July 10, 2018 9:21 AMHere are two examples (and why test both MIN and MAX to be the same?):
Because we don't know what all possible statuses are and if we only test one, we might include records that have additional statuses not in the range tested. If we only test MIN we might include records that also have a SUSPENDED status, and if we only test MAX we might include records that also have a CLOSED status.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 10, 2018 at 9:36 am
John Mitchell-245523 - Tuesday, July 10, 2018 9:26 AMLynn Pettis - Tuesday, July 10, 2018 9:21 AMwhy 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.
July 10, 2018 at 9:45 am
drew.allen - Tuesday, July 10, 2018 9:34 AMLynn Pettis - Tuesday, July 10, 2018 9:21 AMHere are two examples (and why test both MIN and MAX to be the same?):Because we don't know what all possible statuses are and if we only test one, we might include records that have additional statuses not in the range tested. If we only test MIN we might include records that also have a SUSPENDED status, and if we only test MAX we might include records that also have a CLOSED status.
Drew
True, so there is always this:
WITH [BaseData] AS
(
SELECT
[mie].[PartNumber]
, [mie].[PartStatus]
, [rn] = ROW_NUMBER() OVER (PARTITION BY
[mie].[PartNumber]
ORDER BY
CASE [mie].[PartStatus]
WHEN 'OPEN'
THEN 1
ELSE 0
END ASC
)
FROM
@MinimalInventoryExample AS [mie]
)
SELECT
[bd].[PartNumber]
, [bd].[PartStatus]
FROM
[BaseData] AS [bd]
WHERE
[bd].[rn] = 1
AND [bd].[PartStatus] = 'OPEN';
July 10, 2018 at 10:53 am
Lynn Pettis - Tuesday, July 10, 2018 9:45 AMdrew.allen - Tuesday, July 10, 2018 9:34 AMLynn Pettis - Tuesday, July 10, 2018 9:21 AMHere are two examples (and why test both MIN and MAX to be the same?):Because we don't know what all possible statuses are and if we only test one, we might include records that have additional statuses not in the range tested. If we only test MIN we might include records that also have a SUSPENDED status, and if we only test MAX we might include records that also have a CLOSED status.
Drew
True, so there is always this:
WITH [BaseData] AS
(
SELECT
[mie].[PartNumber]
, [mie].[PartStatus]
, [rn] = ROW_NUMBER() OVER (PARTITION BY
[mie].[PartNumber]
ORDER BY
CASE [mie].[PartStatus]
WHEN 'OPEN'
THEN 1
ELSE 0
END ASC
)
FROM
@MinimalInventoryExample AS [mie]
)
SELECT
[bd].[PartNumber]
, [bd].[PartStatus]
FROM
[BaseData] AS [bd]
WHERE
[bd].[rn] = 1
AND [bd].[PartStatus] = 'OPEN';
John's original answer handles multiple OPEN status'es just fine and I think is still the most efficient way to do this:
SELECT
PartNo
, MAX(Status) AS MaxStatus
, MIN(Status) AS MinStatus
FROM Parts
GROUP BY PartNo
HAVING MAX(Status) = 'Open'
AND MIN(Status) = 'Open'';
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 10, 2018 at 12:40 pm
So am I wrong to provide other alternatives? Is so I will just go away.
July 10, 2018 at 12:58 pm
Lynn Pettis - Tuesday, July 10, 2018 12:40 PMSo am I wrong to provide other alternatives? Is so I will just go away.
Absolutely not. Other alternatives give us different ways to think about the same problem and may lead to a more performant solution and can lead to a healthy discussion about what makes different approaches perform better. John and I were both responding to a direct question about why to test both, which is a tangential issue.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 11, 2018 at 6:19 am
Another option.
select Part_No, Part_Status
from TableName tnOpen
where Part_Status = 'Open'
and not exists (select 1 from TableName tnAll where tnAll.Part_No = tnOpen.Part_No and tnAll.Part_Status <> 'Open')
July 13, 2018 at 9:37 pm
saravanatn - Tuesday, July 10, 2018 9:18 AMjeffshelix - Tuesday, July 10, 2018 8:52 AMWe 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 Openi 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2018 at 12:57 am
Jeff Moden - Friday, July 13, 2018 9:37 PMsaravanatn - Tuesday, July 10, 2018 9:18 AMjeffshelix - Tuesday, July 10, 2018 8:52 AMWe 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 Openi 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.
Saravanan
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply