x

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.
more ▼

asked Aug 08, 2011 at 06:58 AM in Default

Ronakshah112 gravatar image

Ronakshah112
38 4 6 8

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Aug 08, 2011 at 07:36 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

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.
Aug 08, 2011 at 07:55 AM Ronakshah112
Thank you so much.. It is working now.
Aug 08, 2011 at 08:31 AM Ronakshah112
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."
Aug 09, 2011 at 10:11 AM Ronakshah112

You would get around that limitation using the query hint "MAXRECURSION".

Add the following to the end of the command batch:

OPTION (MAXRECURSION 0)
This tells the server to keep going - be careful not to code a tight loop though, otherwise it will never end!!
Aug 10, 2011 at 01:25 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Aug 09, 2011 at 12:32 PM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

This works faster than CTEs. I feel :)
Aug 09, 2011 at 03:12 PM Ronakshah112

@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?
Aug 10, 2011 at 01:22 AM WilliamD
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.
Aug 10, 2011 at 04:24 AM Phil Factor

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/
Aug 10, 2011 at 07:59 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x987

asked: Aug 08, 2011 at 06:58 AM

Seen: 599 times

Last Updated: Aug 08, 2011 at 07:21 AM