SQL & PL/SQL

Try this.

update adam_test1 t1
   set balance = (select sum(balance) over(partition by account order by entry_date) val
                        from adam_test t2
                        where t1.rowid = t2.rowid)

Thanks for posting sample data!

If you just want to calculate the running balance you could do the following:

SELECT  ACCOUNT
,       ENTRY_DATE
,       AMOUNT
,       SUM(AMOUNT) OVER (PARTITION BY ACCOUNT ORDER BY ENTRY_DATE) AS BALANCE
FROM    ADAM_TEST1
ORDER BY ACCOUNT
,       ENTRY_DATE

Which results in:

ACCO ENTRY_DATE      AMOUNT    BALANCE
---- ----------- ---------- ----------
1000 01-JAN-2000         50         50
1000 02-JAN-2000         40         90
1000 03-JAN-2000        -50         40
1000 04-JAN-2000         10         50
1000 05-JAN-2000       -250       -200
1000 06-JAN-2000        100       -100
1000 07-JAN-2000         50        -50
2000 01-JAN-2000         30         30
2000 02-JAN-2000         10         40
2000 03-JAN-2000       -520       -480
2000 04-JAN-2000        140       -340
2000 05-JAN-2000         -4       -344
2000 06-JAN-2000        120       -224
2000 07-JAN-2000         57       -167

You may be better off in this case making this calculation at real time instead of trying to store it.

Hi,

Thanks for including the sample data in such a useful form!
Don't forget to post the results you want from that data.

You can get a running total with the analytic SUM function.
For example:

SELECT    a.*
,	  SUM (amount)	OVER ( PARTITION BY  account
	      		       ORDER BY	     entry_date
			     ) 	     AS sum_to_date
FROM	  adam_test1	a
ORDER BY  account
,     	  entry_date
;

Output:

ACCO ENTRY_DAT     AMOUNT    BALANCE SUM_TO_DATE
---- --------- ---------- ---------- -----------
1000 01-JAN-00         50                     50
1000 02-JAN-00         40                     90
1000 03-JAN-00        -50                     40
1000 04-JAN-00         10                     50
1000 05-JAN-00       -250                   -200
1000 06-JAN-00        100                   -100
1000 07-JAN-00         50                    -50
2000 01-JAN-00         30                     30
2000 02-JAN-00         10                     40
2000 03-JAN-00       -520                   -480
2000 04-JAN-00        140                   -340
2000 05-JAN-00         -4                   -344
2000 06-JAN-00        120                   -224
2000 07-JAN-00         57                   -167

If you want to actually store this in the table (which is probably not a good idea), use a MERGE statement, with a query like the one above in the USING clause.

Karthick --

I don't believe the correct results are returned with that update statement. This is what I got:

ACCO ENTRY_DATE      AMOUNT    BALANCE
---- ----------- ---------- ----------
1000 01-JAN-2000         50         50
1000 02-JAN-2000         40         40
1000 03-JAN-2000        -50        -50
1000 04-JAN-2000         10         10
1000 05-JAN-2000       -250       -250
1000 06-JAN-2000        100        100
1000 07-JAN-2000         50         50
2000 01-JAN-2000         30         30
2000 02-JAN-2000         10         10
2000 03-JAN-2000       -520       -520
2000 04-JAN-2000        140        140
2000 05-JAN-2000         -4         -4
2000 06-JAN-2000        120        120
2000 07-JAN-2000         57         57

I believe this is due to the way a correlated subquery executes.

Will not work - analytic functions are applied after where clause. You would need another level of inline view:

update  adam_test1 t1
   set  balance = (
                   select  balance
                     from  (
                            select  t2.rowid rid,
                                    sum(t2.amount) over(partition by t2.account order by t2.entry_date) balance
                              from  adam_test1 t2
                           )
                     where t1.rowid = rid
                   )
/

SY.

Oops i should bring the summing one level down

update adam_test1 t1
   set balance = (select val 
                    from (select rowid rid, sum(balance) over(partition by account order by entry_date) val
                            from adam_test1) t2
                   where t1.rowid = t2.rid)

Thanks to everyone who posted .. loads of options there, testing them out now.

Cheers,
Adam

I've hit a major issue with performance on this script ... table is just too big to do it in one hit.

How would I rewrite it to only consider 100 accounts at a time or something to reduce the load on the DB?

Thanks!
Adam

As Frank pointed out earlier, it's really not a great idea to be storing this information. Do you have (as part of 'your task to add this column' a task to set up the system to properly maintain this column?) If not, then your data is going to be 'corrupt' reasonably quick. And as you're finding out, running a script to adjust the data on a regular basis isn't going to be a good idea.

If you're still set on doing this, then breaking up the records into manageable chunks as you've requested in your other thread that links to this should be a simple TOP-N query.

select 
   account_number
from
(
   select 
      account_number,
      row_number() over (partition by account_number order by account_number asc) as rn
   from YOUR_TABLE
   where balance is null -- this will omit any previously calculated records
)
where rn <= 100

Would get you the 'first' 100 records to process.

DB2 V9.5 allows below OLAP usages ;-)

-- usage1
update adam_test1 t1
   set balance = sum(balance) over(partition by account order by entry_date);

-- usage2
update (select balance,
        sum(balance) over(partition by account order by entry_date) as willSet
          from adam_test)
set balance = willSet;

I hope that Analysis Function 2.0 allows above OLAP usage.
Please test people who has Oracle11gR2 :-)

Hi all,

Sorry to drag this one back up but I've hit an issue with the script .. if the entry_dates are identical the running total doesn't calclulate properly.

Data before script looks like this:

ACCO ENTRY_DATE     AMOUNT    BALANCE
---- ---------- ---------- ----------
1000 01-01-2000         50
1000 01-01-2000         50
1000 02-01-2000         40
1000 03-01-2000        -50
1000 04-01-2000         10
1000 04-01-2000         10
1000 05-01-2000       -250
1000 06-01-2000        100
1000 07-01-2000         50

2000 01-01-2000         30
2000 02-01-2000         10
2000 03-01-2000       -520
2000 04-01-2000        140
2000 04-01-2000        140
2000 05-01-2000         -4
2000 06-01-2000        120
2000 07-01-2000         57

After running the script I get:

ACCO ENTRY_DATE     AMOUNT    BALANCE
---- ---------- ---------- ----------
1000 01-01-2000         50        100
1000 01-01-2000         50        100
1000 02-01-2000         40        140
1000 03-01-2000        -50         90
1000 04-01-2000         10        110
1000 04-01-2000         10        110
1000 05-01-2000       -250       -140
1000 06-01-2000        100        -40
1000 07-01-2000         50         10

2000 01-01-2000         30         30
2000 02-01-2000         10         40
2000 03-01-2000       -520       -480
2000 04-01-2000        140       -200
2000 04-01-2000        140       -200
2000 05-01-2000         -4       -204
2000 06-01-2000        120        -84
2000 07-01-2000         57        -27

The ending balance is still correct but it doesn't run right where the dates are duplicated. The true dates in the DB all include hh:mm:ss and are truly identical timestamps. Is there a way to fix this so that the balance flows correctly?

The script I used was:

merge
  into  adam_test1 t1
  using (
         select  rowid,
                 sum(amount) over(partition by account order by entry_date) balance
           from  adam_test1
        ) t2
  on (t1.rowid = t2.rowid)
  when matched
    then
      update set t1.balance = t2.balance
/ 

..and I'm not calculating this dynamically as the data will be static once the balances are calculated so it'll be a one shot deal.

Thanks!
Adam

Add something unique to it, like ROWID ..

partition by account order by entry_date, rowid

... not tested...

partition by account order by entry_date
means partition by account order by entry_date range unbounded preceding

Therefore you should use
partition by account order by entry_date rows unbounded preceding

:D