Skip to content
Advertisement

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)

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 :

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

the code I modified :

Advertisement

Answer

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

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement