Help with running totals
From: paul <psgoogle_at_sympatico.ca>
Date: 5 Apr 2004 11:11:21 -0700
Message-ID: <b02b1597.0404051011.43b5aead_at_posting.google.com>
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
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')
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