How to compare and aggregate data in a table

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

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

StackExchange Database Administrators Q#130218, answer score: 2