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!