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