I need to write a code for below situation For first day my begining balance will be zero and for next day my begining balance will be ending balance of previous day. CC fro EB = BB+IC+RI-RO-CMP Below is the example Product BB IC RI RO CMP EB RoutDate item1 0 0 0 0 0 0 2/22/2011 item1 0 0 0 2 1 -3 2/23/2011 item1 -3 0 0 0 0 -3 2/24/2011 Item2 0 2 0 0 0 2 2/22/2011 Item2 2 7 0 2 0 7 2/23/2011 Item2 7 0 0 1 0 6 2/24/2011 Intially my BB will be zero for the 1st day and for next day my BB will be ending balance of previous day. Please help me to resolve this Issue. Thanks in advance.
You don't mention the version of SQL Server - I'm expecting 2005 or higher, as this type of thing is much easier with CTEs (available from 2005 onward). This will become even easier when "Denali" gets released into the wild, as that has LAG and LEAD and SUM(ORDER BY) functionality. **EDIT - I dropped the static EB column completely (and made sure to reference the previous ending balance as the current starting balance) to "prove" the calculation is working as expected. If you are wanting to run an update on the table you would change the final select to an update statement to update the static EB column if you were wanting to.** DECLARE @TestTable AS TABLE (Product varchar(10), BB int, IC int, RI int, RO int, CMP int, RouteDate datetime) INSERT INTO @TestTable SELECT 'item1' , 0 , 0 , 0 , 0 , 0 , '2/22/2011'UNION ALL SELECT 'item1' , 0 , 0 , 0 , 2 , 1 , '2/23/2011'UNION ALL SELECT 'item1' , -3 , 0 , 0 , 0 , 0 , '2/24/2011'UNION ALL SELECT 'Item2' , 0 , 2 , 0 , 0 , 0 , '2/22/2011'UNION ALL SELECT 'Item2' , 2 , 7 , 0 , 2 , 0 , '2/23/2011'UNION ALL SELECT 'Item2' , 7 , 0 , 0 , 1 , 0 , '2/24/2011' ; /* CTE "data" assigns a rownumber to each product so we can find the next line for balance calculation */ WITH data AS (SELECT Product, BB, IC, RI, RO, CMP, RouteDate, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY RouteDate) AS rn FROM @TestTable TT), /* rCTE is a recursive CTE to run the balance calculation per day in the correct order */ rCTE AS (SELECT Product, BB, IC, RI, RO, CMP, BB + IC + RI - RO - CMP AS EB, RouteDate, rn FROM data WHERE rn = 1 UNION ALL SELECT cur.Product, prv.EB, cur.IC, cur.RI,
cur.RO, cur.CMP, prv.EB + cur.IC + cur.RI -
cur.RO - cur.CMP AS EB, cur.RouteDate, cur.rn FROM data cur INNER JOIN rCTE prv ON cur.Product = prv.Product -- this join finds the previous value to get the end balance AND cur.rn = prv.rn + 1) SELECT Product, BB, IC, RI, RO, CMP, EB, RouteDate FROM rCTE ORDER BY Product, rn