datediff for row that meets my condition only once per row

I want to do a datediff between 2 dates on different rows only if the rows have a condition.

my table looks like the following, with additional columns (like guid)

Id  | CreateDateAndTime        | condition
---------------------------------------------------------------
1   | 2018-12-11 12:07:55.273  |    with this 
2   | 2018-12-11 12:07:53.550  |    I need to compare this state 
3   | 2018-12-11 12:07:53.550  |    with this 
4   | 2018-12-11 12:06:40.780  |    state 3
5   | 2018-12-11 12:06:39.317  |    I need to compare this state

with this example I would like to have 2 rows in my selection which represent the difference between the dates from id 5-3 and from id 2-1.

As of now I come with a request that gives me the difference between dates from id 5-3 , id 5-1 and id 2-1 :

with t as (
      SELECT TOP (100000) 
          *
      FROM mydatatable

      order by CreateDateAndTime desc) 

      select 
      DATEDIFF(SECOND, f.CreateDateAndTime, s.CreateDateAndTime)  time

      from t f
      join t s on (f.[guid] = s.[guid] )
      where f.condition like '%I need to compare this state%'
      and s.condition like '%with this%'
        and (f.id - s.id) < 0

My problem is I cannot set f.id – s.id to a value since other rows can be between the ones I want to make the diff on.

How can I make the datediff only on the first rows that meet my conditions?

EDIT : To make it more clear

My condition is an eventname and I want to calculate the time between the occurence of my event 1 and my event 2 and fill a column named time for example.

@Salman A answer is really close to what I want except it will not work when my event 2 is not happening (which was not in my initial example)

i.e. in table like the following , it will make the datediff between row id 5 and row id 2

Id  | CreateDateAndTime        | condition
---------------------------------------------------------------
1   | 2018-12-11 12:07:55.273  |    with this 
2   | 2018-12-11 12:07:53.550  |    I need to compare this state 
3   | 2018-12-11 12:07:53.550  |    state 3
4   | 2018-12-11 12:06:40.780  |    state 3
5   | 2018-12-11 12:06:39.317  |    I need to compare this state

the code I modified :

WITH cte AS (
    SELECT id
         , CreateDateAndTime AS currdate
         , LAG(CreateDateAndTime) OVER (PARTITION BY guid ORDER BY id desc ) AS prevdate
         , condition
    FROM t
    WHERE condition IN ('I need to compare this state', 'with this ')
)
SELECT *
     ,DATEDIFF(second, currdate, prevdate) time
FROM cte
WHERE condition = 'I need to compare this state '
and DATEDIFF(second, currdate, prevdate) != 0
order by id desc

Answer

Perhaps you want to match ids with the nearest smaller id. You can use window functions for this:

WITH cte AS (
    SELECT id
         , CreateDateAndTime AS currdate
         , CASE WHEN LAG(condition) OVER (PARTITION BY guid ORDER BY id) = 'with this'
                THEN LAG(CreateDateAndTime) OVER (PARTITION BY guid ORDER BY id) AS prevdate
         , condition
    FROM t
    WHERE condition IN ('I need to compare this state', 'with this')
)
SELECT *
     , DATEDIFF(second, currdate, prevdate)
FROM cte
WHERE condition = 'I need to compare this state'

The CASE expression will match this state with with this. If you have mismatching pairs then it’ll return NULL.

Advertisement

Answer