Sql-server – Compare value from previous record

sql serversql server 2014

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.

Best Answer

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 ;

Related Solutions

Sql-server – T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan.

declare @T table
(
  Eventdate date index IX_Eventdate clustered,
  Val int
);

insert into @T(Eventdate, Val) values
('2012-03-23',  3965),
('2012-03-26',  3979),
('2012-03-27',  3974),
('2012-03-28',  3965),
('2012-03-29',  3967),
('2012-03-30',  3959),
('2012-04-02',  3951),
('2012-04-03',  3961),
('2012-04-04',  3944),
('2012-04-05',  3935),
('2012-04-09',  3901),
('2012-04-10',  3822);

with C as
(
  select top(1) 
         T.Eventdate,
         T.Val,
         T.Val as PrevVal1,
         T.Val as PrevVal2
  from @T as T
  order by T.eventdate
  union all
  select T.Eventdate,
         T.Val,
         T.PrevVal1,
         T.PrevVal2
  from (
       select T.Eventdate,
              T.Val,
              iif(abs(T.Val - C.PrevVal1) <= 12, C.PrevVal1, T.Val) as PrevVal1,
              iif(abs(T.Val - C.PrevVal1) <= 12, null, T.Val) as PrevVal2,
              row_number() over(order by T.Eventdate) as rn
       from @T as T
       inner join C
         on C.Eventdate < T.Eventdate
       ) as T
  where T.rn = 1
)
select C.Eventdate,
       C.PrevVal2 as Val
from C
order by C.Eventdate
option (maxrecursion 0);

Sql-server – How to get the return value from a stored procedure executed with sp_executesql

You need to use the OUTPUT parameter, as it is described in the MSDN article:

Given this dummy stored procedure:

CREATE PROCEDURE sp_test AS
RETURN 2

This code will give you the result:

declare @ret int

exec sp_executesql N'exec @ret = sp_test', N'@ret int OUTPUT', @ret = @ret OUTPUT 
select @ret as result


result
-----------
2

(1 row(s) affected)

Best Answer

Related Solutions

Sql-server – T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

Sql-server – How to get the return value from a stored procedure executed with sp_executesql

Related Question