3

Is there a way to evaluate data in a table to derive if in a set of ProductIDs a PRODUCTID passed or failed? Here is an example of my data:

DECLARE @example TABLE (ID INT NOT NULL, PRODUCTID INT, PASSED BIT, LEVEL INT, CREATEDATE DATETIME, PRIMARY KEY (ID))
INSERT INTO @example VALUES
 (  1, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  2, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  3, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  4, 100, 1, 1, '2016-01-10 07:35:02.123' )
,(  5, 101, 1, 1, '2016-01-10 07:35:02.123' )
,(  6, 102, 0, 1, '2016-01-10 07:35:02.123' )
,(  7, 102, 1, 2, '2016-01-10 07:35:02.123' )
,(  8, 103, 1, 1, '2016-01-10 10:35:02.123' )
,(  9, 103, 1, 2, '2016-01-10 09:35:02.123' )  
,( 10, 103, 1, 2, '2016-01-10 08:35:02.123' ) 
,( 11, 103, 1, 3, '2016-01-10 07:35:02.123' )  

Is it possible to structure a query that returns:

PRODUCTID    ISPASSED
100          0
101          0
102          0
103          1

The conditions are there needs to be 2 PASSED for 2 different LEVELS for a given PRODUCTID.

  • 100 is a 0 because all 4 records are for LEVEL 1
  • 101 is a 0 because there is only 1 PASS and for only 1 LEVEL
  • 102 is a 0 because one record PASSED and the other failed
  • 103 is a 1 because there are 2 PASSED records for different LEVELS.

Thanks so much!

EDIT:

I have come up with this query, but it comes up short because I am only able to qualify first part of the requirements:

WITH cte(PRODUCTID, ISPASSED) AS
(
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) = 2 THEN 1 ELSE 0 END AS ISPASSED
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC), LEVEL, PASSED
    FROM @example
    GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
  WHERE RowNumber <=2
  GROUP BY PRODUCTID
)
select * from cte
  • 6
    Just curious, all of your questions here (except for one old one about free-text search) are extremely similar. Have you made any attempts to apply the solutions from previous questions in order to solve these new problems on your own? – Aaron Bertrand Feb 23 '16 at 18:17
  • More explanation? If one of the 4 entries for 103 did not pass, would the other entries still produce an ISPASSED= 1? – RLF Feb 23 '16 at 18:18
  • @RLF yes it would still produce an ISPASSED = 1 because there are still 2 PASSes for 2 different LEVELS – obautista Feb 23 '16 at 18:22
  • Are there always up to 3 levels or is the number of possible levels variable? – mmarie Feb 23 '16 at 19:14
  • @Aaron Bertran I added more information to the post. I am coming up a little short on my solution. Any help is very much appreciated. – obautista Feb 23 '16 at 19:15
0

This is what I understand... Not sure if this covers all your cases...

Let me know if there's any specific case that isn't covered.

re-edited: just noticed

The conditions are there needs to be 2 PASSED for 2 different LEVELS for a given PRODUCTID.

    Select  productid, 
        Case when minpass = 1 and maxpass = 1 and nbOfLevels>1 and NbOfPass>1 then 1 else 0 end as passed
From (Select Productid, 
            min(convert(int,passed)) minPass,
            max(convert(int,passed)) maxpass, 
            -- max(Level) MaxLevel,
            sum(convert(int,passed)) NbOfPass,
            count(distinct Level) nbOfLevels
            from @example
        group by productid ) X
  • Thank JohnG, but that was catching all cases. I believe I figured it out. I just posted the solution. – obautista Feb 23 '16 at 20:20
  • 1
    @obautista Glad it worked out for you. Just for the fun of it I ran an Execution plan on both (my query and yours) and it came up with the exact same plan. Pretty interesting. – JohnG Feb 23 '16 at 21:17
  • Good call on comparing Execution Plan. – obautista Feb 23 '16 at 22:05
2

Since you want to count differing values in a set that may contain identical values, I would say COUNT(DISTINCT ...) is the perfect tool to use. For instance, the following will count distinct levels per product:

SELECT
  PRODUCTID,
  COUNT(DISTINCT LEVEL)
FROM
  @example
GROUP BY
  PRODUCTID
;

This will be the output for your example:

PRODUCTID
---------  ---
100        1
101        1
102        2
103        3

Now, how can you count only the distinct values matching a specific condition? The answer is, by using conditional aggregation, i.e. one that uses a CASE expression as the aggregate function's argument. So, to count only distinct passed levels, you can use a query like this:

SELECT
  PRODUCTID,
  COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END)
FROM
  @example
GROUP BY
  PRODUCTID
;

And the results will now be

PRODUCTID
---------  ---
100        1
101        1
102        1
103        3

The figures seem correct now, so how can you turn those 1, 3 into 0, 1? Why, by using another CASE expression, of course:

SELECT
  PRODUCTID,
  ISPASSED = CASE
                WHEN COUNT(DISTINCT CASE PASSED WHEN 1 THEN LEVEL END) >= 2
                THEN 1
                ELSE 0
              END
FROM
  @example
GROUP BY
  PRODUCTID
;

Now you can enjoy the expected output of

PRODUCTID  ISPASSED
---------  --------
100        0
101        0
102        0
103        1
1

This will give you the desired answer based upon the example data you provided. You didn't answer my question about the number of levels, so this assumes there are no more than 3 levels.

select ProductID, case when [1]+ [2] + [3] >=2  then 1 else 0 end as ispassed
FROM 
(Select cast(PASSED as int) PASSED, Level, Productid
FROM @example) e 
PIVOT 
( 
Max(Passed) 
FOR [Level] in ([1], [2], [3]) )
AS pvt 
ORDER BY pvt.PRODUCTID
  • if I change the last record for 100 to: ,( 4, 100, 1, 2, '2016-01-10 07:35:02.123' ) the query still return ispassed = 0. Would expect it to be 1 since there are 2 PASSes for 2 different LEVELS. – obautista Feb 23 '16 at 19:44
  • There are no more than 3 levels – obautista Feb 23 '16 at 19:55
0

This appears to be working:

WITH cte(ProductID, IsPassed) AS
{
  SELECT PRODUCTID, CASE WHEN SUM(CONVERT(INT, PASSED)) >= 2 THEN 1 ELSE 0 END AS IsPassed
  FROM (SELECT PRODUCTID, RowNumber = ROW_NUMBER() OVER(PARTITION BY PRODUCTID, LEVEL ORDER BY CREATEDATE DESC), LEVEL, PASSED
        FROM @example
        GROUP BY PRODUCTID, LEVEL, CREATEDATE, PASSED) as agg
WHERE RowNumber = 1
GROUP BY PRODUCTID
)
select * from cte
  • 1
    Why the cte? You can remove it if you only do a select *. – Julien Vavasseur Feb 23 '16 at 20:27
  • I simply copied and pasted. Actual query is part of a larger query. – obautista Feb 23 '16 at 22:06
  • You should mention it in you answer and explain why it's working. As it is, it should not be there. Your answer would attract more upvotes. – Julien Vavasseur Feb 23 '16 at 22:08

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.