SQL Server – Get 2 Most Recent Records by Product

sql serversql server 2014

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.

Best Answer

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
Related Question