Stack Exchange
SQL Server 2008: LAG and runningTotals
CREATE TABLE #NetAmounts   
(
    priority int,
    NetAmount decimal(10, 2),
    DedicatedAmt decimal(10, 2)
)

INSERT INTO #NetAmounts (priority, NetAmount, DedicatedAmt)
VALUES (1, 6000, 2500),
       (2, 6000, 2500),
       (3, 6000, 2500),
       (4, 6000, 2500)

Net Amount is always same throughout.
I want to update Dedicated Amount so that the sum of dedicated amount is always equals to the netamount by priority order
Expected Result:
 Priority  NetAmount  DedicatedAmt
 ---------------------------------
    1         6000       2500
    2         6000       2500
    3         6000       1000
    4         6000       0

My query (not working)
;WITH CTE AS 
(  
   SELECT 
      *, 
      RunningTotal = SUM(DedicatedAmt) OVER(ORDER BY 1) 
   FROM #NetAmounts 
)
SELECT
   Priority,
   Netamount,
   DedicatedAmt = CASE
                     WHEN DedicatedAmt > RunningTotal  
                       THEN CASE
                              WHEN LAG(Netamount, 1, Netamount) OVER(ORDER BY Priority) >= DedicatedAmt  
                               AND RunningTotal <= Netamount
                               THEN ABS(LAG(Netamount, 1, Netamount) OVER(ORDER BY Priority) - DedicatedAmt)
                               ELSE 0
                             END
                       ELSE DedicatedAmt
                    END
FROM CTE

Thanks

change order by 1 to order by Priority

LAG is a new keyword in SQL Server 2012 - it is not available in SQL Server 2008 yet ...
