Help comparing different columns on different rows.

[gordread]

I have event related data where I need to compare the end date of one event to the start date of the next event.  Each event is on a separate row.  Each event has a start and and end date.

example
Event      Start Date      End Date
Event 1   2015-06-01   2015-06-05
Event 2   2015-06-06   2015-06-12
Event 3   2015-06-15   2015-06-22

I would like to know the difference between the end and start date of consecutive events.

example
Event      Start Date      End Date        Prev Start Date   Difference
Event 1   2015-06-01   2015-06-05   2015-06-06        1
Event 2   2015-06-06   2015-06-12   2015-06-15        3
Event 3   2015-06-15   2015-06-22   Null                     Null

I can't for the life of me figure out how to get the previous start date into my current row.

Any help would be greatly appreciated.

Gordon

[gordread]

So I've got something working, but I'd really like to know if there is a better way.

I created a query that gave me the data for each row and I added a running count for the whole query, as well as a running count -1.

RC Event      Start Date      End Date        RC-1
1    Event 1   2015-06-01   2015-06-05   0
2    Event 2   2015-06-06   2015-06-12   1
3    Event 3   2015-06-15   2015-06-22   2

Then I duplicated the query, and joined the original and the duplicate on RC and RC-1 (1-1 to 0-1)

This allowed me to build the following data set
Event      Start Date      End Date       Prev Start Date   Prev End Date
Event 1   2015-06-01   2015-06-05   2015-06-06        2015-06-12
Event 2   2015-06-06   2015-06-12   2015-06-15        2015-06-22
Event 3   2015-06-15   2015-06-22   Null                     Null

I was then able to do the calculations that I needed.

Any thoughts would be appreciated, but this does work.

Gordon