Lag and Lead in SQL
Lag and Lead in SQL are used to get values from the previous row or next row. Below we will just discuss Lag, you can figure Lead out, same logic, it just gets the next row
The syntax is as follows so you can actually have offsets and default values too. Offsets are used if you don't want the previous value, but the value 2 or 3 rows back for example.
https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15
For example with this data
We could order this by Name and Week as follows
And now suppose we want a new calculated column showing the previous weeks sales. We can use Lag to do this as follows
Notice how we move the Order by from the end of the query and put it into the Lag section. Not sure why this is and you can actually still have a normal order by. If this matches what is in the Lag statement then all seems to be fine but otherwise it just seems to go a bit haywire. So best to make them match or just have one in the Lag.
One shortcoming here is that the first week for Asda shows the previous weeks sales for Aldi. We need some way of grouping by Customer Name.
We do this as follows using Partition By as follows
