SQL SERVER LEAD FORMULA

SQL SERVER LEAD FORMULA

-- THEORY

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

-- LEAD([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,
LEAD(RESELLER.AnnualRevenue,1,0) OVER (ORDER BY RESELLER.FIRSTORDERYEAR) AS ORDER_YEAR_NEXT_AMOUNT
FROM AdventureWorksDW2012.dbO.DimReseller AS RESELLER
WHERE RESELLER.FirstOrderYear IS NOT NULL;