Get 2 Most Recent Records By Product

Question

I am stumped. I need to return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different.

PRODUCTID  CREATEDATE    STATUS    LEVEL
ABC        1/3/2016      fail      1
ABC        1/4/2016      pass      2
ABC        1/5/2016      pass      3
DEF        1/1/2016      pass      1
DEF        1/2/2016      pass      1
DEF        1/10/2016     pass      1
DEF        1/11/2016     fail      1
GHI        12/29/2015    pass      1
GHI        12/30/2015    fail      1
JKL        1/1/2016      pass      1
JKL        1/2/2016      pass      1

Based on values above the following would be returned:

PRODUCTID   RUNSTATUS
ABC         TRUE
DEF         FALSE
GHI         FALSE
JKL         FALSE

Any help is greatly appreciated.

• UPDATED POST with additional logic.

I added LEVEL column. The query needs to return as described above.

Return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different.

If a ProductID has only one record total, and it is PASS, it would be FALSE. There needs to be two 'pass' in a row.

Answer 1

The below query works like this:

• It first uses the ROW_NUMBER window function to partition it by PRODUCTID and ORDER them by CREATEDATE from 1 to n
• For STATUS = 'pass' you give 1 (0 otherwise)
• You then only keep the first 2 values with n <= 2 (-> 1 or 2)
• Finally if the sum of the 2 rows for a given PRODUCTID is equal to 2 and the count of distinct LEVELs is 2, it returns TRUE.

Query:

SELECT PRODUCTID
    , CASE WHEN SUM(STATUS) = 2 AND COUNT(DISTINCT LEVEL) = 2
          THEN 'TRUE'
          ELSE 'FALSE'
      END
FROM (
    SELECT PRODUCTID, LEVEL
        , STATUS = CASE WHEN STATUS = 'pass' THEN 1 ELSE 0 END
        , n = ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY CREATEDATE DESC)
    FROM data
) agg
WHERE n <= 2
GROUP BY PRODUCTID

See SQL Fiddle.

Output:

PRODUCTID   RUNSTATUS
ABC         TRUE
DEF         FALSE
GHI         FALSE
JKL         FALSE

Answer 2

Another solution using LAG function:

Query:

WITH passes AS(
    SELECT ProductId
          ,CreateDate
          ,CASE WHEN Status = 'pass'
                AND LAG(Status) OVER(PARTITION BY ProductId ORDER BY CreateDate) = 'pass'
                THEN 'TRUE'
                ELSE 'FALSE'
                END
           AS Pass
    FROM Table1
  )
  SELECT ProductId
        ,Pass
FROM passes p
WHERE CreateDate = (
  SELECT MAX(CreateDate) FROM passes
  WHERE ProductId = p.ProductId
)