SQL SERVER LAG FORMULA

SQL SERVER LAG FORMULA

-- THEORY

-- SELECT [COLUMN ID KEY], [DATE AS YEAR FORMAT] AS [ALIAS COLUMN NAME], [AMOUNT IN MONEY],

-- LAG([AMOUNT IN MONEY],1,0) OVER (ORDER BY [DATE AS YEAR FORMAT]) AS [ALIAS COLUMN NAME]

-- FROM [DATABASE NAME].[DATABASE OBJECT].[TABLE NAME] AS [TABLE ALIAS NAME]

-- WHERE [TABLE ALIAS NAME].[DATE AS YEAR FORMAT] IS NOT NULL;

-- EXAMPLE

SELECT RESELLER.ResellerKey, RESELLER.FirstOrderYear AS FIRSTYEAR, RESELLER.AnnualRevenue,
LAG(RESELLER.AnnualRevenue,1,0) OVER (ORDER BY RESELLER.FIRSTORDERYEAR) AS ORDER_YEAR_AMMOUNT_PREVIOUS
FROM AdventureWorksDW2012.dbO.DimReseller AS RESELLER
WHERE RESELLER.FirstOrderYear IS NOT NULL;