Lead & Lag Functions

A reporting requirement I’ve seen come up several times is the ability to compare a field value from one record with the value in the same field but a previous record, or else some other requirement to get the preceding or proceeding record. For example, you need to compare each measurement value with the previous measurement value in order to work out the % increase or decrease since the last time. And you usually need to do this per category (per person, per store etc.)

There are several ways to do this. I used to use self joins in order to link a table to itself but where each record links to the previous record within each category. This works fine but is a bit fiddly and hard to read. As of SQL Server 2012, we can use window functions as a shortcut to get the same result. Window functions use the idea of breaking down a table full of records into separate ‘windows’, in order to apply functions to each of these subsets rather than just on the whole dataset. A bit like using GROUP BY, but you can do different things. The basic syntax is

SELECT function() OVER ( PARTITION BY Field1 ORDER BY Field2 )

Field1 is your category, so you might want to split your table up by person or store. Equally, you can put multiple fields in here e.g. to get a combination of person and store. Field2 obviously determines what order your records will be in before the function is applied. Lead and Lag are the functions used to get the next and previous records respectively. The syntax is

SELECT LAG ( Field1, OffsetNumber, DefaultValue)

Field1 provides the value you want to retrieve. offsetNumber is how many records you want to go forward or back, so to get previous or next, use 1. DefaultValue is the value returned if nothing is found. You can use other analytic, ranking and aggregate functions, so check them out!

Using Lag function to retrieve, for each sale, the previous amount for that person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s