Get the overall status of a particular product

  • kavern

    SSC Rookie

    Points: 31

    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.

  • Lowell

    SSC Guru

    Points: 323463

    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

    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: 442360

    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: 323463

    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

    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: 86893

    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: 3529

    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: 186120

    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'

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Smash125

    Hall of Fame

    Points: 3529

    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: 186120

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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Smash125

    Hall of Fame

    Points: 3529

    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: 186120

    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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • aaron.reese

    SSChampion

    Points: 13415

    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: 98561

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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