SQL SERVER LAST VALUE FORMULA

SQL SERVER LAST VALUE FORMULA

--THEORY

-- SELECT [COLUMN ID KEY], [YEAR COLUMN] AS [ALIAS COLUMN NAME], [MONEY COLUMN],

-- LAST_VALUE([YEAR COLUMN]) OVER (PARTITION BY [MONEY COLUMN] ORDER BY [YEAR COLUMN]) AS LAST_VALUE

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

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

-- EXAMPLE

SELECT RESELLER.ResellerKey, RESELLER.FirstOrderYear AS FIRSTYEAR, RESELLER.AnnualRevenue,
LAST_VALUE(RESELLER.FirstOrderYear) OVER (PARTITION BY RESELLER.ANNUALREVENUE ORDER BY RESELLER.FIRSTORDERYEAR) AS LAST_VALUE
FROM AdventureWorksDW2012.dbO.DimReseller AS RESELLER
WHERE RESELLER.FirstOrderYear IS NOT NULL;