Skip Navigation LinksHome > Categories > Code from a Category

Fetching data from the next or previous rows of the result set


User Name: tippu
Name: King SheZ
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
7 years of enterprise application development, so far in my career i have worked on enterprise web applications with millions of users,i have also worked on many integrations between applications, i h... [More]
Viewed Times: 2302
Add Date: 09/05/2011
One common problem is the need to have data on a result set row from another row in the same result set. For example, in order to calculate an interval between dates on two separate rows, typically a self join is needed. This requires to add the same table (or set) twice into the query. With a simple query, this won't be any problem but if the result set is created using a more complex query, creating the same complexity twice can cause problems and of course create performance problems.

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 AddTimePrevEvent PrevTimeNextEvent
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.203E#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 LookBackPrevTimeIntervalInHours LookForward
E#5 First event NULL NULL Next: E#5 -> E#1
E#1E#5 -> E#12011-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#22011-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.34327 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.

Post a Comment
Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below: 		(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.