Compare Past Records to Most Recent Record

Question

Is it possible to structure a single query that would return a 0 or 1 (true/false) by comparing first record to previous records?

Sample records in table:

ProductID    SampleDate    RunFailure    LevelCode
101          20160322      0             2
101          20160321      0             3
101          20160320      1             2
101          20160318      1             2
102          20160317      0             3
102          20160316      0             2
102          20160315      0             1
103          20160314      1             1
103          20160313      0             2
103          20160312      1             3

I need to evaluate the most recent record by ProductID. In the example above the last record (on 20160322) has a LevelCode of 2. It needs to be compared to the record on 20160320 because it is the last record that failed with the same LevelCode (so it would ignore the record on 20160318 because it is older than the record on the 20th), therefore, in this case the query would return 1 (or true).

For ProductID 102 the query would return 0 (or false) because the LevelCode on 20160317 is 3 and there isnt an older record that failed with that same LevelCode.

ProductID 103 would return a 0 (false) because on 20160314 the LevelCode is 1 and the last record for that ProductID that failed was on 20160312 was for LevelCode3

Ideally the query would return something like this:

ProductID    IsMostCurrentRunSameLevelAsLastFailedRun
101          1
102          0
103          0

Answer 1

One way would be using CROSS APPLY.

If you identify the most recent record for each product in the first table and then pass the criteria into the cross apply.

SELECT 
      ProductId
   ,  MAX(CASE WHEN t1.RunFailure = t2.RunFailure THEN 1 ELSE 0 END) AS IsMostCurrentRunSameLevelAsLastFailedRun
   FROM <SomeTable> t1
   CROSS APPLY (
      SELECT TOP 1
         RunFailure
         FROM <SomeTable> st
         WHERE st.ProductId = t1.ProductId
         AND   st.LevelCode = t1.LevelCode
         AND   st.SampleDate < t1.SampleDate
         ORDER BY st.SampleDate) AS t2
   GROUP BY t1.ProductId

Alternatively, look into the windowing functions. You can do a great deal with them that may be more efficient.