asked
table with changing value between coulmn
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
Initially I stored BB & EB value in table set to 0. I am getting correct EB value but that EB will not transfered to nextday BB value. Please help.
In above table everyday probably 100 rows will be
inserted.So there are about 30000 rows. Once I ran this query for certain date it gives me this error. Any idea "The maximum recursion 100 has been exhausted before statement completion."
I'd do it a lot simpler and avoid all that CTE stuff by using Quirky Update. CREATE TABLE #TestTable ( Product VARCHAR( 10 ),bb INT DEFAULT NULL,ic INT,ri INT,ro INT,cmp INT, eb INT DEFAULT NULL,RouteDate DATETIME, CONSTRAINT pk_PersonId PRIMARY KEY (Product, RouteDate) ); INSERT INTO #TestTable (product,IC,RI,RO,CMP, routedate) SELECT 'item1' , 0 , 0 , 0 , 0 , '2/22/2011' UNION ALL SELECT 'item1' , 0 , 0 , 2 , 1 , '2/23/2011' UNION ALL SELECT 'item1' , 0 , 0 , 0 , 0 , '2/24/2011' UNION ALL SELECT 'Item2' , 2 , 0 , 0 , 0 , '2/22/2011' UNION ALL SELECT 'Item2' , 7 , 0 , 2 , 0 , '2/23/2011' UNION ALL SELECT 'Item2' , 0 , 0 , 1 , 0 , '2/24/2011' ; DECLARE @Product VARCHAR(10), @eb int, @BB int SELECT @Product='', @BB=0, @EB=0 UPDATE #TestTable SET @BB=BB=CASE WHEN @product<>product THEN 0 ELSE @EB END, @
EB=EB=@BB+IC+RI-RO-CMP, @Product=product SELECT * FROM #TestTable
I always ring-fence this by using a temp table or table variable to which I can apply a clustered index in order to impose the order I want. You'd have to take this into account if you are using this technique to update a base table. It might affect performance comparisons. Sure, you can do the same thing on a base table if have the correct clustered index, but you have to be very careful to check the results, and retest on every version change.
@Ronakshah112 - it is faster because you just run through the table once, whereas my solution joins back onto the table using recursion (will always be slower). Nicely done @Phil Factor (+1), I still haven't played with quirky updates enough. Have you compared it to the new sum(order by) in denali?
Sadly, I have personally experienced the unwanted behaviour of the quirky update, but it was due to my own stupidity which allowed me to disregard the effects of parallellism, which made sense in case when the table has millions of records. It taught me to always include the **option (maxdop 1)** in the quirky update statements I still love, which are still the best and can be made bulletproof by following simple guidelines outlined by @Jeff Moden in this [fascinating 22 pages long article][1]. One thing will always remain true: if you get the quirky update right then the same query will always work correctly in any version of the SQL Server which exists now. [1]:
http://www.sqlservercentral.com/articles/T-SQL/68467/
