Re: Help with running totals

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: 5 Apr 2004 19:25:27 -0700
Message-ID: <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 - 04:25:27 CEST