lag.sql

/* Let's review your sales, Jenkins... */
WITH sales AS (
    SELECT  h.SalesPersonID ,
            ROW_NUMBER() OVER (PARTITION BY h.SalesPersonID
                               ORDER BY     DATEPART(yyyy, h.OrderDate),
                                            DATEPART(mm, h.OrderDate)) AS rn ,
            DATEPART(yyyy, h.OrderDate) AS [Year],
            DATEPART(mm, h.OrderDate) AS [Month],
            SUM(h.SubTotal) AS TerritoryTotal
    FROM    Sales.SalesOrderHeader AS h
    WHERE   SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID ,
            DATEPART(yyyy, OrderDate) ,
            DATEPART(mm, OrderDate)
)
SELECT  s.SalesPersonID,
        s.[Year],
        s.[Month],
        s.TerritoryTotal,
        s1.TerritoryTotal AS last_month ,
        s3.TerritoryTotal AS three_months_ago ,
        s6.TerritoryTotal AS six_months_ago ,
        s9.TerritoryTotal AS nine_months_ago ,
        s12.TerritoryTotal AS twelve_months_ago
FROM    sales AS s
        LEFT OUTER JOIN sales AS s1 ON s.SalesPersonID = s1.SalesPersonID
                                       AND s.rn = s1.rn + 1
        LEFT OUTER JOIN sales AS s3 ON s.SalesPersonID = s3.SalesPersonID
                                       AND s.rn = s3.rn + 3
        LEFT OUTER JOIN sales AS s6 ON s.SalesPersonID = s6.SalesPersonID
                                       AND s.rn = s6.rn + 6
        LEFT OUTER JOIN sales AS s9 ON s.SalesPersonID = s9.SalesPersonID
                                       AND s.rn = s9.rn + 9
        LEFT OUTER JOIN sales AS s12 ON s.SalesPersonID = s12.SalesPersonID
                                       AND s.rn = s12.rn + 12 ;

/* Pretty sweet, eh? I love it when you do this. 
   It's how I get a new SAN. */






/* EasyMode.On() */
SELECT  the_year,
        the_month,
        SalesPersonID, 
        TerritoryTotal,
        LAG(TerritoryTotal, 1, 0.00) OVER (PARTITION BY SalesPersonID
                                           ORDER BY the_year, the_month) AS one_month_ago,
        LAG(TerritoryTotal, 3, 0.00) OVER (PARTITION BY SalesPersonID 
                                           ORDER BY the_year, the_month) AS three_months_ago,
        LAG(TerritoryTotal, 6, 0.00) OVER (PARTITION BY SalesPersonID 
                                           ORDER BY the_year, the_month) AS six_months_ago,
        LAG(TerritoryTotal, 9, 0.00) OVER (PARTITION BY SalesPersonID 
                                           ORDER BY the_year, the_month) AS nine_months_ago,
        LAG(TerritoryTotal, 12, 0.00) OVER (PARTITION BY SalesPersonID 
                                            ORDER BY the_year, the_month) AS twelve_months_ago
FROM (
        SELECT    SalesPersonID, 
                DATEPART(yyyy, OrderDate) AS the_year, 
                DATEPART(MONTH, OrderDate) AS the_month, 
                DATEPART(QUARTER, OrderDate) AS the_quarter,
                SUM(SubTotal) AS TerritoryTotal
        FROM    Sales.SalesOrderHeader 
        GROUP BY SalesPersonID, 
                DATEPART(yyyy, OrderDate), 
                DATEPART(month, OrderDate), 
                DATEPART(QUARTER, OrderDate)
) AS x
WHERE    SalesPersonID IS NOT NULL
ORDER BY SalesPersonID, 
        the_year, 
        the_month;

Testing