You've heard my rant before "measure what you think you are measuring". If not, follow the link. Here's an example using the LAG function, new in SQL Server Denali, to measure sales trends. We'll start with a view from AdventureWorksDW2008R2 (the double cast is for clarity).

CREATE VIEW dbo.EmployeeSalesByMonth
AS
SELECT  EmployeeKey as [Employee],
        DATEPART(yyyy, CAST(CAST(OrderDateKey as varchar(8)) as DATE)) AS [Year] ,
        DATEPART(mm, CAST(CAST(OrderDateKey as varchar(8)) as DATE)) AS [Month] ,
        SUM(SalesAmount) AS [EmployeeTotal]
FROM    dbo.FactResellerSales
WHERE    EmployeeKey IS NOT NULL
GROUP BY EmployeeKey,
        DATEPART(yyyy, CAST(CAST(OrderDateKey as varchar(8)) as DATE)),
        DATEPART(mm, CAST(CAST(OrderDateKey as varchar(8)) as DATE))
GO

Let's use LAG to get sales for this month, previous month, and three months ago. I'm choosing a specific employee, just leave the WHERE clause out to get everyone.

SELECT  Employee,
        [Year] ,
        [Month] ,
        EmployeeTotal AS SalesThisMonth,
        LAG(EmployeeTotal, 1, 0.00) OVER (PARTITION BY Employee ORDER BY [Year], [Month]) AS SalesLastMonth ,
        LAG(EmployeeTotal, 3, 0.00) OVER (PARTITION BY Employee ORDER BY [Year], [Month]) AS SalesThreeMonthsAgo
FROM dbo.EmployeeSalesByMonth
WHERE Employee = 272
ORDER BY Employee, [Year], [Month];
GO

The LAG function works perfectly. Or does it…? Let's look at the first few rows.

                               This Month      Last Month      Three Months Ago
272     2005    8       20544.7015      0.00               0.00
272     2005    9       2039.994         20544.7015     0.00
272     2005    11      6341.551        2039.994         0.00
272     2006    2       61206.4782      6341.551        20544.7015
272     2006    3       18307.746       61206.4782     2039.994

Although this sounded like a good idea, and the function is working as advertised, the answer isn't what we want. Look at the 2005-11 line, for example. Last month was 2005-10, but there was no sales that month, so we get 2005-9 sales. Three months ago (2005-08) there were sales, but we get zero. What gives?

We're only producing rows for months where there were sales for employee 272. Probably not what you'd want. Although we think we're counting months with this LAG function, we're actually counting ROWS. SQL Server BOL implementation of LAG states that the offset (first parameter) is "The number of rows back from the current row from which to obtain a value."

So, can we fix things to get "reasonable" values? Because LAG uses rows, we'd need to come up with some blank (zero total) rows for months where there are no sales. Some databases have a special data densification syntax, like "partition-by joins" to fill in the gaps. Using Itzik Ben-Gan's dbo.GetNums TVF, we can make a table of allMonths and all Employees between a date range. (Note: this code is a quick hack and pretty fragile, but we will end up with the right answer. It also assumes you have dbo.GetNums in tempdb, see the SQLMag article for that function).

DECLARE
 @startdt AS DATE = '20050701',  — first date we care about
 @enddt   AS DATE = '20080731'   — last date we care about
 
SELECT DATEADD(month, n-1, @startdt) AS dt, EmployeeKey AS Employee
INTO #allMonths
FROM tempdb.dbo.GetNums(DATEDIFF(month, @startdt, @enddt) + 1) AS Nums
CROSS JOIN
(
SELECT DISTINCT EmployeeKey
FROM dbo.FactResellerSales
) AS A; 
GO

With this table in hand, we can use an OUTER APPLY to "add in" the months with no sales, and our sales trend query becomes:

WITH SalesByMonth AS
(
SELECT Employee,
       DATEPART(yyyy,dt) as Year,
    DATEPART(MONTH,dt) as Month,
    ISNULL(EmployeeTotal, 0) as EmployeeTotal
FROM #allMonths m
OUTER APPLY (
SELECT  EmployeeTotal
FROM    EmployeeSalesByMonth e
WHERE m.Employee = e.Employee  
 AND    DATEPART(yyyy,dt) = [Year]
 AND    DATEPART(mm,dt) = [Month]
) AS t
)
SELECT  Employee,
        [Year] ,
        [Month] ,
        EmployeeTotal AS SalesThisMonth,
        LAG(EmployeeTotal, 1, 0.00) OVER (PARTITION BY Employee ORDER BY [Year], [Month]) AS SalesLastMonth ,
        LAG(EmployeeTotal, 3, 0.00) OVER (PARTITION BY Employee ORDER BY [Year], [Month]) AS SalesThreeMonthsAgo
FROM SalesByMonth
WHERE Employee = 272
ORDER BY Employee,
        [Year],
        [Month];
GO

A few corresponding rows…the sales figures now agree with the column headings

                               This Month      Last Month      Three Months Ago
272     2005    8       20544.7015      0.00               0.00
272     2005    9       2039.994         20544.7015     0.00
272     2005    10     0.00                2039.994         0.00
272     2005    11     6341.551         0.00               20544.7015
272     2005    12     0.00                6341.551        2039.994
272     2006    1       0.00                0.00                0.00
272     2006    2       61206.4782     0.00                 6341.551
272     2006    3       18307.746       61206.4782      0.00

So remember…although the default window for the OVER clause really is RANGE UNBOUNDED PRECEDING AND CURRENT ROW, the LAG function only counts ROWs, not RANGEs.

@bobbeauch