Compare value from previous record

Question

How can I compare value from previous records?

Table:

ID    SAMPLEDATE    LEVEL    PASSED
1     1/1/2016      0        1
2     1/2/2016      1        1
3     1/3/2016      2        0
4     1/4/2016      1        1
5     1/5/2016      2        0

Is the last record (ordered by SAMPLEDATE) the same LEVEL as the last record that failed? If yes, return TRUE otherwise return FALSE.

So in my example data the last record that failed with LEVEL 2. Then the last record (before the 1/5/2016 record) that failed on 1/3/2016 was the same LEVEL, so TRUE would be returned. If it was a different LEVEL FALSE would be returned.

Answer 1

I assumed that "failed" means passed = 0.

There are some detail missing from the question, i.e. do we need to compare the very last row (when ordered by sampledate) with the previously last "failed" row? If yes, then the following query will do:

with last_row as
( select top (1) sampledate, level
  from dbo.tablex
  -- where failed = 0 
  order by sampledate desc
)
select top (1) result =
    case when t.level = r.level 
        then 1 else 0
    end 
from dbo.tablex as t
  join last_row as r 
  on t.sampledate < r.sampledate
where t.passed = 0
order by t.sampledate desc ;

---

If, on the other side, we want to compare the last "failed" row with the previous "failed" row, then we could uncomment the where failed = 0 line above or use the less complicated, just find the last 2 failed rows and compare their levels with a simple group by:

with last_2_failed_rows as
( select top (2) sampledate, level
  from dbo.tablex
  where failed = 0 
  order by sampledate desc
)
select result =
    case when min(level) = max(level) 
        then 1 else 0
    end 
from last_2_failed_rows ;