How to compare and aggregate data in a table

Question

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

Answer 1

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

Answer 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

Answer 3

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

Answer 4

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