Creating a Running Total in PL-SQL (that refers to itself)

[CrystalizeCanada]

Hi,
I’m struggling with a Running Total problem.
I’m attempting to create a view so I’m typing a PL-SQL statement that I think requires the use of ‘Oracle Analytics functions’ like Sum() Over and/or maybe even Lag () Over?.
It can't be a stored procedure - the result needs to be a view so I will need to put CREATE VIEW MyView ...before the statement and make it a view when I'm ready.

MY EXAMPLE
I’m given an Allowance throughout the week.
It happens to be 10 dollars but it can vary from day to day.
I can create a running total with SUM(Amt) Over etc...
This is the CUMUL column in the example below.
On certain days I’ve spent different percentages of the allowance. (The SPENT Column which is a field in the database)
I can't manage to create the AMTLEFT column in the example below.
The AmtLeft column seems to be a kind of running total that 'refers to itself' so this is where I'm stumped.

Week,Day,Amt,Cumul,Spent,AmtLeft
1,Mon,10,10,0%,10
1,Tue,10,20,50%,10
1,Wed,10,30,0%,20
1,Thu,10,40,0%,30
1,Fri,10,50,20%,32
1,Sat,10,60,0%,42
--------------------
2,Mon,10,10,00%,10
2,Tue,10,20,00%,20
etc...

My imaginary SQL would look something like at this point (if I have it right):

SELECT Week, Day, Amt, Sum(Amt) Over (Partition By Week, Order By Day) AS Cumul, Spent FROM AllowancesTable

How to get the last column AmtLeft?

Any help would be greatly appreciated...

Thanks
Gordon

Gordon BOCP
Crystalize

 

[oradba101]

Why don't you write a REF CURSOR package? I had to do it for a Crystal Report that needed to be able to define parameters before the report was run

William Chadbourne
Programmer/Analyst

 

[CrystalizeCanada]

Hi William,

Thanks for the reply.

REF CURSOR would be something to explore. I think that would make it a Stored Procedure (vs a view) and for now I'd like to try to create a view as I've got a bunch of views already.

I had a suggestion to use the MODEL analytic function:
(I haven't had chance to look at it but it seems to work. Here is what I was given)

SELECT Week,
Day,
Amt,
Cumul,
Spent,
AmtLeft
FROM Allowances
MODEL
PARTITION BY(Week)
DIMENSION BY(
CASE Day
WHEN 'Mon' THEN 1
WHEN 'Tue' THEN 2
WHEN 'Wed' THEN 3
WHEN 'Thu' THEN 4
WHEN 'Fri' THEN 5
WHEN 'Sat' THEN 6
WHEN 'Sun' THEN 7
END D
)
MEASURES(
Day,
Amt,
Amt Cumul,
Spent,
Amt * (1 - Spent) AmtLeft
)
RULES(
Cumul[D > 1] ORDER BY D = Cumul[cv() - 1] + Amt[cv()],
AmtLeft[D > 1] ORDER BY D = (AmtLeft[cv() - 1] + Amt[cv()]) * (1 - Spent[cv()])
)
ORDER BY Week,
D

Thanks
Gordon


Gordon BOCP
Crystalize

 

[LKBrwnDBA]

Or you could try this:

SQL> WITH mytab AS
  2      ( SELECT 1 week, 'Mon' dayx, 10 amt, 10 cumm, 0 pct, 10 amtleft FROM DUAL UNION
  3        SELECT 1, 'Tue', 10, 20,50, 10 FROM DUAL UNION
  4        SELECT 1, 'Wed', 10, 30, 0, 20 FROM DUAL UNION
  5        SELECT 1, 'Thu', 10, 40, 0, 30 FROM DUAL UNION
  6        SELECT 1, 'Fri', 10, 50,20, 32 FROM DUAL UNION
  7        SELECT 1, 'Sat', 10, 60, 0, 42 FROM DUAL UNION
  8        SELECT 2, 'Mon', 10, 10, 0, 10 FROM DUAL UNION
  9        SELECT 2, 'Tue', 10, 20, 0, 20 FROM DUAL)
 10  SELECT *
 11  FROM (SELECT week, dayx, amt,
 12               SUM(amt )
 13               OVER ( PARTITION BY week
 14                      ORDER BY INSTR( 'Mon,Tue,Wed,Thu,Fri,Sat,Sun', dayx )) cumm,
 15               pct,
 16               (LAG(amtleft,1,0 )
 17               OVER ( PARTITION BY week
 18                      ORDER BY INSTR( 'Mon,Tue,Wed,Thu,Fri,Sat,Sun', dayx )) +
             amt )* ( 1 - pct / 100 )
 19   20                 amtleft
 21        FROM mytab)
 22  /

      WEEK DAYX             AMT       CUMM        PCT    AMTLEFT
---------- --------- ---------- ---------- ---------- ----------
         1 Mon               10         10          0         10
         1 Tue               10         20         50         10
         1 Wed               10         30          0         20
         1 Thu               10         40          0         30
         1 Fri               10         50         20         32
         1 Sat               10         60          0         42
         2 Mon               10         10          0         10
         2 Tue               10         20          0         20

8 rows selected.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[taupirho]

LKBrwnDBA,

A very good try and indeed it works for the specific set of data the OP gave as an example but your solution does not work in general terms. Try changing the pct spent on the first Monday to something other than 0 for example and you will see that the change taht this causes does not propogate through to subsequent data etc ...

CrystalizeCanada,

If you do not want to write procedural code, the MODEL clause is absolutley the way to go for solving these "chasing your tail" types of problem in Oracle.


In order to understand recursion, you must first understand recursion.

 

[CrystalizeCanada]

Thanks for the replies.

The MODEL method is probably route I will take for this problem

Tx

Gordon

Gordon BOCP
Crystalize