INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

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

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
 

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

(OP)
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

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


Or you could try this:

CODE

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.
3eyes

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

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

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.

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

(OP)
Thanks for the replies.

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

Tx

Gordon

Gordon BOCP
Crystalize

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close