CODEWITHSUNDEEP
sql-servert-sqllaglead
Mike
Mike

Reputation: 1261

T-SQL Issue using lead or lag

I have a table that has columns EVENT_ACTION and TIMESTAMP; in column EVENT_ACTION there are two possible values, 225 and 226.

225 represent the start_time and 226 represent the end_time; since they are in two different rows I'm trying to use LAG or LEAD and have some issues.

Here is what I have so far; the column MRDF is my unique id: 

SELECT  
    f.EVENT_ACTION ,
    (f.TIMESTAMP) AS starttime, 
    LEAD(f.TIMESTAMP) OVER (ORDER BY f.MRDF) AS endtime 
FROM  
    dbo.flext f 
WHERE 
    EVENT_ACTION IN (225,226) 
ORDER BY 
    MRDF, EVENT_ACTION

This is what I'm getting: it's now getting the next row's timestamp as I thought it would:

Query Results

I'm getting a null value for my last EVENT_ACTION 255. I'm planing to place this into a temp table and only take EVENT_ACTION 225

As you can see I'm lost :-).

Any help would be appreciated

Mike

Upvotes: 1

Views: 629

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

I think you want to use f.TIMESTAMP as your ORDER BY for the LEAD(). I think your query should look something more like this:

SELECT  
    f.EVENT_ACTION ,
    (f.TIMESTAMP) AS starttime, 
    LEAD(f.TIMESTAMP) OVER (ORDER BY f.TIMESTAMP ASC) AS endtime 
FROM  
    dbo.flext f 
WHERE 
    EVENT_ACTION IN (225,226) 
ORDER BY MRDF, EVENT_ACTION

However, this will still leave you with a NULL for the endtime of your last 226 record. So you can add a default value to the LEAD() function for this situation. The syntax is:

LEAD ( scalar_expression [ ,offset ] , [ default ] )

Using this syntax, your LEAD() would then become:

LEAD(f.TIMESTAMP, 1, GETDATE()) OVER (ORDER BY f.TIMESTAMP ASC) AS endtime

You can replace the GETDATE() with whatever you'd want the default value to be when there is no leading record.

Upvotes: 2

Related Questions