In SQL Server Code-Named “Denali”, CTP 3, two functions with windowing support are introduced to address this problem: LAG (to look backwards) and LEAD (to look forward). Both can be instructed to look over several records, not just the previous or next.
First we need a simple table with some test data:
And some data with random dates in the future:
Now, in order to fetch the following information:
•What row I'm on?
•What was the previous event?
•What's the datetime on previous row?
•What's the interval in hours between the previous and this event?
•What event pair is going to be next?
First, an inline view (named sub) is used to gather the data. Since LAG and LEAD are window functions, the ordering must be specified. In this example the AddTime column defines the order of the rows.
An example output from the inline view could look like the following:
Event | AddTime | PrevEvent | PrevTime | NextEvent |
E#5 | 2011-07-29 05:17:44.680 | NULL | NULL | E#1 |
E#1 | 2011-07-29 18:30:02.053 | E#5 | 2011-07-29 05:17:44.680 | E#4 |
E#4 | 2011-08-01 01:39:16.430 | E#1 | 2011-07-29 18:30:02.053 | E#10 |
E#10 | 2011-08-01 14:32:50.040 | E#4 | 2011-08-01 01:39:16.430 | E#2 |
E#2 | 2011-08-02 19:30:16.483 | E#10 | 2011-08-01 14:32:50.040 | E#8 |
E#8 | 2011-08-02 20:20:19.920 | E#2 | 2011-08-02 19:30:16.483 | E#9 |
E#9 | 2011-08-03 01:23:00.940 | E#8 | 2011-08-02 20:20:19.920 | E#6 |
E#6 | 2011-08-03 02:09:39.343 | E#9 | 2011-08-03 01:23:00.940 | E#7 |
E#7 | 2011-08-04 05:30:01.203 | E#6 | 2011-08-03 02:09:39.343 | E#3 |
E#3 | 2011-08-05 11:16:57.253 | E#7 | 2011-08-04 05:30:01.203 | NULL |
The main query is basically just formatting, concatenation of event column data, interval calculation etc. The actual output would be like:
ActualRow | LookBack | PrevTime | IntervalInHours | LookForward |
E#5 | First event | NULL | NULL | Next: E#5 -> E#1 |
E#1 | E#5 -> E#1 | 2011-07-29 05:17:44.680 | 13 | Next: E#1 -> E#4 |
E#4 | E#1 -> E#4 | 2011-07-29 18:30:02.053 | 55 | Next: E#4 -> E#10 |
E#10 | E#4 -> E#10 | 2011-08-01 01:39:16.430 | 13 | Next: E#10 -> E#2 |
E#2 | E#10 -> E#2 | 2011-08-01 14:32:50.040 | 29 | Next: E#2 -> E#8 |
E#8 | E#2 -> E#8 | 2011-08-02 19:30:16.483 | 1 | Next: E#8 -> E#9
|
E#9 | E#8 -> E#9 | 2011-08-02 20:20:19.920 | 5 | Next: E#9 -> E#6
|
E#6 | E#9 -> E#6 | 2011-08-03 01:23:00.940 | 1 | Next: E#6 -> E#7 |
E#7 | E#6 -> E#7 | 2011-08-03 02:09:39.343 | 27 | Next: E#7 -> E#3 |
E#3 | E#7 -> E#3 | 2011-08-04 05:30:01.203 | 30 | Last event |
Having these two functions makes it easier to get data from next or previous rows.