4

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.

4

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
1

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
)
  • So sorry, but I need additional logic. I added the information in the original post. – obautista Jan 26 '16 at 1:54

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.