Get the overall status of a particular product

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Get the overall status of a particular...

15 posts, Page 1 of 2

1 2 »»

Get the overall status of a particular product

Rate Topic

Display Mode

Topic Options

Author

Message

kavern

Posted Wednesday, September 19, 2012 3:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 3:38 AM
Points: 3, Visits: 6

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.

Post #1361633

Lowell

Posted Wednesday, September 19, 2012 3:07 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789, Visits: 28,063

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1361638

kavern

Posted Wednesday, September 19, 2012 3:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 3:38 AM
Points: 3, Visits: 6

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?

Post #1361647

Lynn Pettis

Posted Wednesday, September 19, 2012 3:26 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 21,832, Visits: 27,851

kavern (9/19/2012)

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

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1361656

Lowell

Posted Wednesday, September 19, 2012 3:30 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789, Visits: 28,063

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1361659

kavern

Posted Wednesday, September 19, 2012 11:14 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 3:38 AM
Points: 3, Visits: 6

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.

Post #1361736

dwain.c

Posted Thursday, September 20, 2012 12:32 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 5:02 PM
Points: 2,369, Visits: 3,247

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

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1361753

Smash125

Posted Tuesday, October 02, 2012 7:44 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 07, 2013 3:53 AM
Points: 161, Visits: 840

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

Post #1367039

ChrisM@Work

Posted Tuesday, October 02, 2012 8:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 5,705, Visits: 11,132

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1367071

Smash125

Posted Tuesday, October 02, 2012 8:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 07, 2013 3:53 AM
Points: 161, Visits: 840

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

Post #1367088

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions