Search code examples
sqlsql-serverwindow-functions

SQL Lag and Lead With Mutiple Partitions

I have the following query where I am trying to display the previous month's value using the LAG function. Because I am also including the year, I am trying to partition the data by both year and month. However, this is returning null. If I limit the partition to just the year, I do get the previous month's values for Feb - Dec, but January of the second year is null instead of having December's value (as shown in the image)

Is there a way to do this while still using the LAG and LEAD window functions?

;WITH CTE (Yy, Mm, NetTotal)
AS (
    SELECT 
        DATEPART(yy, o.OrderDate) Yy,
        DATEPART(mm, o.OrderDate) Mm,
        SUM(ol.Quantity * ol.UnitPrice)
        FROM Sales.Orders o
        INNER JOIN Sales.OrderLines ol on ol.OrderId = o.OrderId
        GROUP BY 
        DATEPART(yy, o.OrderDate),
        DATEPART(mm, o.OrderDate)

)

select 
Yy,
Mm,
NetTotal,
SUM(NetTotal) OVER (PARTITION BY Yy) AnnualTotal,
SUM(NetTotal) OVER (PARTITION BY Yy, Mm) MonthlyTotal,
LAG(NetTotal, 1) OVER (PARTITION BY YY, mm ORDER BY YY, Mm)
from CTE
order by Yy, Mm

enter image description here

Running against the WideWorldImporters sample db, if that's any help.

Solution

  • From what you say, you don't what partition by at all, just order by:

    LAG(NetTotal) OVER (ORDER BY YY, Mm)

    You don't need the 1 for LAG() because that is the default.