lag() and lead() sql server function to select data from diff table

Question

I need help to be able to do the following:

MainTable has

ID (Primary Key)
DATE_TIME
STATE

SubTable has

ID (Primary Key)
DATE_TIME
LINE1

When MainTable.STATE value changes from 100 to 200, I want to select SubTable.Line1 value where MainTable.DATE_TIME = SubTable.DATE_TIME

It seems that either LAG() or LEAD() function can detects this transition of data in MainTable.STATE field.

How can do this with SQL statement using LAG() or LEAD() function?

Thanks for all the quick responses. Since MainTable.STATE value could be anything, I can't include its value in the sql stmt.

Answer 1

Here is a solution:

SELECT *, CASE WHEN State = 200 AND LAG(State) OVER(ORDER BY ID) = 100 
               THEN (SELECT TOP 1 Line1 FROM SubTable WHERE DATE_TIME = m.DATE_TIME) END 
FROM MainTable m

Answer 2

This should do the trick.

SELECT Line1
FROM SubTable s
INNER JOIN 
(
    SELECT DATE_TIME, State, LAG(State) OVER(ORDER BY DATE_TIME) as LastState
    FROM MainTable 
)
m
ON s.DATE_TIME = m.DATE_TIME
WHERE State = 200
AND LastState = 100

Answer 3

Assuming I understand your question, you can do something like this:

select Line1
  from (
select T1.[Id] as T1Id,
       T1.[Key] as T1Key,
       T1.[State] as T1State,
       T2.[Id] as T2Id,
       T2.[FKey] as T2Key,
       T2.[Line] as T2Line1,
       lag(T1.[State], 1, 100) OVER (order by T1.[Id]) as PriorState
  from T1
       left outer join T2 on T1.[Key] = T2.FKey
) as Data
 where [T1State] <> PriorState
   and [T1State] = 200