Get the overall status of a particular product

Question

Post reply

Get the overall status of a particular product

kavern

SSC Rookie

Points: 31
More actions
September 19, 2012 at 3:04 pm

#393217

Hi, need some help to write the query as i am not sure how to achieve this.
I am having a table for example which consist of items such as laptop.
The table columns are
Product | Parts | Status
==================
Laptop1 mouse OK
Laptop1 screen OK
Laptop1 button OK
Laptop2 mouse OK
Laptop2 screen OK
Laptop2 button NOT OK
I would like to a query where it shows the results as below
Product | Overall Status
===================
Laptop1 OK
Laptop2 NOT OK
Appreciate any help and thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

assuming alphabetical order of the status can be used, this works:

With MyCTE (Product,Parts,Status)

AS

(

SELECT 'Laptop1','mouse','OK' UNION ALL

SELECT 'Laptop1','screen','OK' UNION ALL

SELECT 'Laptop1','button','OK' UNION ALL

SELECT 'Laptop2','mouse','OK' UNION ALL

SELECT 'Laptop2','screen','OK' UNION ALL

SELECT 'Laptop2','button','NOT OK'

)

select Product, MIN(status)

FROM MyCTE

GROUP BY Product

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!

kavern SSC Rookie Points: 31 More actions · Answer 2

That is assuming i know all the products and i can type one at a time. I was just giving an example on how the table data will look like which will have many data.

Is there other query without hard coding the query?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

kavern (9/19/2012)
That is assuming i know all the products and i can type one at a time. I was just giving an example on how the table data will look like which will have many data.
Is there other query without hard coding the query?

Lowell's CTE is simply a way for him to provide test data for the actual query. Substitute your actual table for his CTE in the select part of the query.

select Product, MIN(status)

FROM yourTable

GROUP BY Product

Lowell SSC Guru Points: 323518 More actions · Answer 4

iit depends on your data, if any status other than OK, then it fails, for example?

With MyCTE (Product,Parts,Status)

AS

(

SELECT 'Laptop1','mouse','OK' UNION ALL

SELECT 'Laptop1','screen','OK' UNION ALL

SELECT 'Laptop1','button','OK' UNION ALL

SELECT 'Laptop2','mouse','OK' UNION ALL

SELECT 'Laptop2','screen','OK' UNION ALL

SELECT 'Laptop2','button','NOT OK'

)

select T1.Product, CASE

WHEN T2.Product IS NOT NULL

THEN 'Failed'

ELSE 'OK'

END

FROM MyCTE T1

LEFT OUTER JOIN (SELECT Product FROM MyCTE WHere Status <> 'OK') T2

ON T1.Product = T2.Product

GROUP BY T1.Product,T2.Product

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!

kavern SSC Rookie Points: 31 More actions · Answer 5

Thank Lowell. I am nearly there. The latest example you gave 'by saying having anything other than OK' gives the status NOT OK.

I should have mentioned it earlier. This is because i have another status which is PASS (technician test). OK is another double verification to close the item. (final). PASS + OK = OK PASS + OK + NOT OK = NOT OK.

As long as there exist NOT OK in the group of product, overall is NOT OK.

I have change the condition in select statement where Status = 'NOT OK' instead of Status <> 'OK'

Thanks again.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

Not sure but considering your latest requirements, I think a slight modification to Lowell's original suggestion might perform better:

With MyCTE (Product,Parts,Status)

AS

(

SELECT 'Laptop1','mouse','OK' UNION ALL

SELECT 'Laptop1','screen','OK' UNION ALL

SELECT 'Laptop1','button','PASS' UNION ALL

SELECT 'Laptop2','mouse','OK' UNION ALL

SELECT 'Laptop2','screen','OK' UNION ALL

SELECT 'Laptop2','button','NOT OK' UNION ALL

SELECT 'Laptop3','button','OK' UNION ALL

SELECT 'Laptop3','screen','PASS' UNION ALL

SELECT 'Laptop3','mouse','PASS' UNION ALL

SELECT 'Laptop4','button','PASS' UNION ALL

SELECT 'Laptop4','screen','PASS' UNION ALL

SELECT 'Laptop4','mouse','PASS' )

select Product

, status=CASE MIN(status)

WHEN 'OK' THEN MIN(status)

WHEN 'PASS' THEN 'OK'

ELSE 'NOT OK' END

FROM MyCTE

GROUP BY Product

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Smash125 Hall of Fame Points: 3562 More actions · Answer 7

CREATE TABLE #TestData

(

Product NVARCHAR(100),

Parts NVARCHAR(100),

Status NVARCHAR(100)

)

INSERT INTO #TestData(Product,Parts,Status)

SELECT 'Laptop1','mouse','OK' UNION ALL

SELECT 'Laptop1','screen','OK' UNION ALL

SELECT 'Laptop1','button','OK' UNION ALL

SELECT 'Laptop2','mouse','OK' UNION ALL

SELECT 'Laptop2','screen','OK' UNION ALL

SELECT 'Laptop2','button','NOT OK'

This can be achieved using RANKING FUNCTION

WITH C AS

(

SELECT *,ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Product) AS Rn FROM #TestData

),

C1 AS

(

SELECT *, MAX(Rn) OVER(PARTITION BY Product)AS MaxRn FROM C

)

SELECT Product,Status FROM C1 WHERE Rn=MaxRn

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Change the order of your test data and see of this solution works:

DROP TABLE #TestData

CREATE TABLE #TestData

(

Product NVARCHAR(100),

Parts NVARCHAR(100),

Status NVARCHAR(100)

)

INSERT INTO #TestData(Product,Parts,Status)

SELECT 'Laptop1','mouse','OK' UNION ALL

SELECT 'Laptop1','screen','OK' UNION ALL

SELECT 'Laptop1','button','OK' UNION ALL

SELECT 'Laptop2','mouse','OK' UNION ALL

SELECT 'Laptop2','button','NOT OK' UNION ALL

SELECT 'Laptop2','screen','OK'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Smash125 Hall of Fame Points: 3562 More actions · Answer 9

You are correct. Just wanted to know in one of the queries

select Product, MIN(status)

FROM MyCTE

GROUP BY Product

the second column says MIN(status) just wanted to know which row its directing and why. May i am asking some basic and stupid question but i need to know

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Smash125 (10/2/2012)
You are correct. Just wanted to know in one of the queries
select Product, MIN(status)
FROM MyCTE
GROUP BY Product
the second column says MIN(status) just wanted to know which row its directing and why. May i am asking some basic and stupid question but i need to know

MIN will apply to all rows in the partition (Product) and return the value which is the smallest in the set. In most collations, 'N' is smaller than (will precede) 'O'.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Smash125 Hall of Fame Points: 3562 More actions · Answer 11

How about this one correct me if i am wrong

;WITH C AS

(

SELECT *,ROW_NUMBER() OVER(PARTITION BY Status ORDER BY Product) AS Rn FROM #TestData

),

C1 AS

(

SELECT *,MIN(Rn)OVER(PARTITION BY Status) MinRn FROM C

)

SELECT Product,Status FROM C1 WHERE Rn=MinRn

ORDER BY Product ASC

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Smash125 (10/2/2012)
How about this one correct me if i am wrong
;WITH C AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Status ORDER BY Product) AS Rn FROM #TestData
),
C1 AS
(
SELECT *,MIN(Rn)OVER(PARTITION BY Status) MinRn FROM C
)
SELECT Product,Status FROM C1 WHERE Rn=MinRn
ORDER BY Product ASC

Yes, it's wrong. The partition should be Product, not status:

;With TestData (Product,Parts,Status) AS (

SELECT 'Laptop1','mouse','OK' UNION ALL

SELECT 'Laptop1','screen','OK' UNION ALL

SELECT 'Laptop1','button','PASS' UNION ALL

SELECT 'Laptop2','mouse','OK' UNION ALL

SELECT 'Laptop2','screen','OK' UNION ALL

SELECT 'Laptop2','button','NOT OK' UNION ALL

SELECT 'Laptop3','button','OK' UNION ALL

SELECT 'Laptop3','screen','PASS' UNION ALL

SELECT 'Laptop3','mouse','PASS' UNION ALL

SELECT 'Laptop4','mouse','PASS' UNION ALL

SELECT 'Laptop5','button','PASS' UNION ALL

SELECT 'Laptop5','screen','PASS' UNION ALL

SELECT 'Laptop5','mouse','PASS' ),

C AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Status ORDER BY Product) AS Rn FROM TestData),

C1 AS (SELECT *,MIN(Rn)OVER(PARTITION BY Status) MinRn FROM C)

SELECT Product, Status, Rn, MinRn

FROM C1 --WHERE Rn=MinRn

ORDER BY Product ASC, Status

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

aaron.reese SSChampion Points: 13498 More actions · Answer 13

This is an indication that your data is poorly structured.

you are using a text field to represent a binary value (Pass/fail)

I would suggest the introduction of a lookup table

declare @TestStatus as table

(

TestStatusDescription nvarchar(50),

TestResult int

)

insert into @testStatus (TestStatusDescription,TestResult) values ('OK',1)

insert into @testStatus (TestStatusDescription,TestResult) values ('Pass',1)

insert into @testStatus (TestStatusDescription,TestResult) values ('Fail',0)

insert into @testStatus (TestStatusDescription,TestResult) values ('Not OK',0)

...

one record for every description in your table: you can find these by doing a SELECT DISTINCT on the relevant field.

Then join to this @TestStatus table to return 1 or 0 and use the MIN() functionality to determine whether any test has failed.

ScottPletcher SSC Guru Points: 101105 More actions · Answer 14

An uncontrolled MIN() could fail as other Statuses appear in the table.

I suggest directly coding for the specific condition you are looking for:

SELECT

Product,

MIN(CASE WHEN Status = 'NOT OK' THEN 'NOT OK' ELSE 'OK' END) AS [Overall Status]

FROM dbo.tablename

GROUP BY

Product

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".