Re: Help with running totals

thanks for the input
what you have shown me is the inner query I have before the LAG -the default RANGE when not specified is UNBOUNDED PRECEDING which in this case gives me ENDING balances that I lag(1,0) to get opening balances. ie: 19903 open_bal=0, mon_ttl=-172527, end_bal=-172527

What I want is to makeup data at the end of the range and keep the ending balance going forward which is a little different than my original request.
your recognition that i want to dump the lag and show the ending_balance (and calculate the opening by taking off the current activity has helped.
What I really want is this

    DT BUCKET RUN_TTL
------ ---------- ----------

199903 0192          -172527
199906 0192           374180
199909 0192            95496
199910 0192          1195635
199911 0192          1690914
199912 0192                0
200003 0192          1025431
200006 0192          -706247
200009 0192          1273009
200012 0192                0
200101 0192                0
200102 0192                0
[..]
200301 0192                0

I am trying in 10G now with partitioned outer joins, but that does not seem to work if going off of analytic queries, only straight base tables.

douglashawthorne_at_yahoo.com.au (Douglas Hawthorne) wrote in message news:<cf15dee4.0404051825.7a0760dc_at_posting.google.com>...
> psgoogle_at_sympatico.ca (paul) wrote in message news:<b02b1597.0404051011.43b5aead_at_posting.google.com>...
> > I have a query that takes monthly totals and rolls them up to get a
> > balance at a specific time code
> >
> > eg:
> > dt bucket mon_ttl
> > --- ------ -------
> > 199903 0192 -172527
> > 199906 0192 546707
> > 199909 0192 -278684
> > 199910 0192 1100139
> > 199911 0192 495279
> > 199912 0192 -1690914
> > 200003 0192 1025431
> > 200006 0192 -1731678
> > 200009 0192 1979256
> > 200012 0192 -1273009
> >
> >
> >
> > Rolls up like
> >
> > DT BUCKET OPEN
> > --------- ---------- ----------
> > 199903 0192 0
> > 199906 0192 -172527
> > 199909 0192 374180 (-172527+546707)
> > 199910 0192 95496 etc...
> > 199911 0192 1195635
> > 199912 0192 1690914
> > 200003 0192 0
> > 200006 0192 1025431
> > 200009 0192 -706247
> > 200012 0192 1273009
> >
> > I get that using
> >
> > select dt, bucket, lag(run_ttl,1,0)
> > over (order by dt) open
> > from
> > (select dt, bucket, ttl,sum(ttl)
> > over (partition by bucket order by dt) run_ttl
> > from
> > mon_tot
> > where bucket='0192')
> >
> > But I want to keep that last balance going forward...
> > eg: if I want the open as at 200301 and 200012 was the last non-null
> > balance
> > I want to carry that forward.
> > 200012 0192 1273009
> > 200101 0192 1273009
> > [..]
> > 200301 0192 1273009
> >
> > But I need to 'make up' the data going forward
> >
> > I'm on 9.2.0.3 anyone have any tips/ideas?
>
>
> You were almost there except you did not have the RANGE UNBOUNDED
> PRECEDING clause. My suggestion is as follows:
> SELECT
> dt,
> bucket,
> SUM( ttl )
> OVER(
> PARTITION BY bucket
> ORDER BY dt
> RANGE UNBOUNDED PRECEDING
> ) run_ttl
> FROM
> mon_tot
> WHERE
> bucket='0192'
> ;
>
> My test results are:
> DT BUCK RUN_TTL
> ------ ---- ----------
> 199903 0192 -172527
> 199906 0192 374180
> 199909 0192 95496
> 199910 0192 1195635
> 199911 0192 1690914
> 199912 0192 0
> 200003 0192 1025431
> 200006 0192 -706247
> 200009 0192 1273009
> 200012 0192 0
>
> 10 rows selected.
>
> I found this hint on p.560 of "Expert One-on-One Oracle" by Thomas
> Kyte (A-Press:2003). He also has an on-line example at
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:305416600201
>
> Douglas Hawthorne
Received on Tue Apr 06 2004 - 17:04:35 CEST