By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,220 Members | 1,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,220 IT Pros & Developers. It's quick & easy.

Help with running totals

P: n/a
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?
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ps******@sympatico.ca (paul) wrote in message news:<b0**************************@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...D:305416600201

Douglas Hawthorne
Jul 19 '05 #2

P: n/a
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

How can 'project' that last row forward?

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.

do**************@yahoo.com.au (Douglas Hawthorne) wrote in message news:<cf**************************@posting.google. com>...
ps******@sympatico.ca (paul) wrote in message news:<b0**************************@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...D:305416600201

Douglas Hawthorne

Jul 19 '05 #3

P: n/a
"paul" <ps******@sympatico.ca> wrote in message
news:b0**************************@posting.google.c om...
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

How can 'project' that last row forward?

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.

do**************@yahoo.com.au (Douglas Hawthorne) wrote in message

news:<cf**************************@posting.google. com>...
ps******@sympatico.ca (paul) wrote in message news:<b0**************************@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...D:305416600201
Douglas Hawthorne


Paul,

What you want is a source of rows that have the generated DT and BUCKET
columns with NULL for the TTL column. My suggested solution is to use a
'temporary' table to store the projected values of DT for every BUCKET
value. I then modified my original query to be over an in-line view that is
a UNION ALL of the original table and the projected values table.

First, I created the 'temporary' table to hold the projected values using
CTAS and selecting no rows:
CREATE TABLE projected_mon_tot
AS SELECT * FROM mon_tot
WHERE 1=0
;

Second, I populated this table based on the current values from the MON_TOT
table:
INSERT INTO projected_mon_tot
SELECT
TO_CHAR(
ADD_MONTHS(
TO_DATE(
t.base_dt,
'YYYYMM'
),
a.num_months
),
'YYYYMM'
) AS dt,
t.bucket,
NULL AS ttl
FROM
(
SELECT
rownum AS num_months
FROM
all_objects
WHERE
rownum <= 25
) a,
(
SELECT
MAX( dt ) AS base_dt,
bucket
FROM
mon_tot
GROUP BY
bucket
) t
;

Here I am projecting 25 months past the last DT value for each value in the
BUCKET column by using a cartesian join between a range of row numbers from
the ALL_OBJECTS table and the calculated maximum value of DT for each value
of BUCKET. I have assumed that the DT column is of data type, VARCHAR2 or
CHAR.

Third, I ran the modified query:
SELECT
dt,
bucket,
SUM( ttl )
OVER(
PARTITION BY bucket
ORDER BY dt
) run_ttl
FROM
(
SELECT
*
FROM
mon_tot
UNION ALL
SELECT
*
FROM
projected_mon_tot
)
WHERE
bucket='0192'
;

The only difference between this query and my original one is the use of the
in-line view with the UNION ALL. I used UNION ALL because I know that there
are no overlaps in values between the two tables.

Thanks for pointing out that RANGE UNBOUNDED PRECEEDING is the default.

The result is:

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
200101 0192 0
200102 0192 0
200103 0192 0
200104 0192 0
200105 0192 0
200106 0192 0
200107 0192 0
200108 0192 0
200109 0192 0
200110 0192 0
200111 0192 0
200112 0192 0
200201 0192 0
200202 0192 0
200203 0192 0
200204 0192 0
200205 0192 0
200206 0192 0
200207 0192 0
200208 0192 0
200209 0192 0
200210 0192 0
200211 0192 0
200212 0192 0
200301 0192 0

35 rows selected.

Douglas Hawthorne
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.