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
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