Help with running totals
Date: 5 Apr 2004 11:11:21 -0700
Message-ID: <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:
Rolls up like
DT BUCKET OPEN
I get that using
select dt, bucket, lag(run_ttl,1,0)
over (order by dt) open
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
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
--------- ---------- ----------
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
from
(select dt, bucket, ttl,sum(ttl)
over (partition by bucket order by dt) run_ttl
from
mon_tot
where bucket='0192')
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? Received on Mon Apr 05 2004 - 20:11:21 CEST