1

I am stuck on how to return a bit value based on records with certain values. Query needs to return TRUE or FALSE if there are three different Levels of the same Control:

Example data:

PRODUCT    CONTROL    LEVEL
test1      8          1
test1      8          2
test1      8          3
test2      8          1
test2      8          1
test2      8          3
test4      8          1
test4      8          3
test4      8          7

I need a query that would return:

PRODUCT    STATUS
test1      PASS
test2      FAIL
test4      PASS

Not sure where to begin. Any tips are greatly appreciated.

  • 1
    what if there are more than 3 like 1, 2, 3 and 4 and/or duplicate 1, 2, 3 and 2? does it work? – Julien Vavasseur Jan 25 '16 at 20:16
  • Yes - that will work too. The only criteria is there needs to be three different levels of the same control. – obautista Jan 25 '16 at 20:22
  • It's not clear if you want GROUP BY product or GROUP BY product, control. Does the values of Control stay the same? – ypercubeᵀᴹ Jan 25 '16 at 20:29
  • and do you need 2 rows when there are 2 control for a product with 3 level or only 1 row? – Julien Vavasseur Jan 25 '16 at 20:40
3

This query will return PASS if there are at least three unique levels, otherwise FAIL.

SELECT PRODUCT, (CASE
       WHEN COUNT(DISTINCT [LEVEL])>=3 THEN 'PASS'
       ELSE 'FAIL' END) AS [STATUS]
FROM tablenamegoeshere
GROUP BY PRODUCT, [CONTROL];

COUNT(DISTINCT ..) counts the number of unique values of a column or expression. CASE returns different values depending on the outcome of different conditions. In this case, if the number of unique values is equal to or greater than three, we want to return 'PASS' otherwise 'FAIL'.

Obviously, if you want to return a bit value (i.e. boolean), you could change the strings 'PASS' and 'FAIL' in the code into 1 and 0 respectively and cast them to bit using CAST(... AS bit).

| improve this answer | |
  • Well, that would be a bit value, not boolean (there is no boolean in SQL Server), and only if you explicitly cast it as such (otherwise it will be interpreted as an int). – Aaron Bertrand Jan 25 '16 at 20:26
  • Language barrier. :) Updated my answer with the correct wording. – Daniel Hutmacher Jan 25 '16 at 20:30
  • Sorry, my wording was wrong too, I knew that you meant boolean, it doesn't exist. – Aaron Bertrand Jan 25 '16 at 20:30
1
WITH ctePassFail(Product,Control,ControlCount)
AS
(   
    SELECT PRODUCT, CONTROL, COUNT(DISTINCT LEVEL)
    FROM ProductTest
    GROUP BY PRODUCT, CONTROL
)
SELECT Product, CASE WHEN ControlCount >= 3 'PASS' ELSE 'FAIL' END STATUS
FROM ctePassFail
| improve this answer | |
0

To add to the other answers, this query gives PASS or FAIL for each unique Product having 1 or more Controls with at least 3 different Levels for one at least one of the Controls:

SELECT PRODUCT
    , CASE WHEN MAX(total) >= 3 THEN 'PASS' ELSE 'FAIL' END
FROM (
    SELECT PRODUCT, total = COUNT(DISTINCT LEVEL)
    FROM data
    GROUP BY PRODUCT, CONTROL
) as g
GROUP BY PRODUCT
;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.