Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get the overall status of a particular product Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, September 19, 2012 3:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,916, Visits: 32,077
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
Posted Wednesday, September 19, 2012 3:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, September 19, 2012 3:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:47 PM
Points: 23,396, Visits: 32,229
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





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
Posted Wednesday, September 19, 2012 3:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 12,916, Visits: 32,077
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
Posted Wednesday, September 19, 2012 11:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, September 20, 2012 12:32 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 3,648, Visits: 5,322
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1361753
Posted Tuesday, October 2, 2012 7:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:19 AM
Points: 200, Visits: 1,138
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
Posted Tuesday, October 2, 2012 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Posted Tuesday, October 2, 2012 8:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:19 AM
Points: 200, Visits: 1,138
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 »

Add to briefcase 12»»

Permissions Expand / Collapse