Window Functions Part 2

In my last post I started explaining what Window Functions in SQL Server are and how to use ROW_NUMBER().

To continue with the series, today I’m bringing you another post on Window Functions, now I’ll show you how to use the functions LAG() and LEAD().

Suppose you manage a business with employees that work on different shifts (Morning, Afternoon and Night shifts). Currently, you have something like this:

You want to know when is the next date all employees will work on a Night Shift for a given date. Doing it manually will be a very tedious work, so you come up with an idea of a query and you do something like this:

Here we can see when all employees will work night shift. Let’s see an example of how we can achieve the same.

LEAD()

We’re going to use the LEAD() window function. The way it works is, it gets a peak at the next row within a result set, using a partitioning item. Let’s see the example:

Since we want to know the next shift date, we use ShiftDate as an argument for the function. The 1 in there is how many rows ahead we want to see. The PARTITION BY is optional in the syntax, but if you recall the way partitioning works, you realize we use it because we want to divide the result set by EmployeeName and ShiftName. Remember that ORDER BY is required. If we compare both queries, they return the same results.

Before wrapping up, let’s see a very simple example on LAG() function. It works the same way LEAD() does, but instead of looking at the next row, LAG() is able to look at the previous row.

Let’s suppose we want to know what was the previous shift the employee worked at a certain date. The query would be like this:

Couple more things to note here, there’s another argument to the function which is optional, that argument is the default value you would like to show if any of your rows is returning a null. The other thing is, we’re not taking performance into consideration here, we will cover that later in the blog.

Here’s the link to the code I used for this examples:
https://github.com/learningsql/sql-code

I hope you guys liked the post, please share and like. See you soon, cheers!