[Oracle] Help with a calculated field that involves partitioning data and running totals

Here is my table which you can plug in to your favorite client and try it if you wish.

This is a screen shot of the table is produces, with the section highlighted in blue as the problem I’m trying to solve.

I’m trying to calculate a “distribution” of a payment. In a list of employees who are owed a payment and they can define one or more accounts and one or more fixed allocations and percentage allocations, I need to calculate each allocation. Say your payment is $1000, and you want 20% of it in your primary account, a fixed $50 into your kid’s account, and any remainder into some other account.

My code works fine except in the situation of empid #4. This person has defined a bunch of fixed dollar distributions but their payment doesn’t cover it. Since going negative as a remainder is not an option, the remainder should be zero but also the calculated distributions should stop at some point down the list with respect to the account priority/rank.

This is the result I’m looking for.

Code for demo table:

with mytable as ( select 1 empid, 1000 amount, 12345 account, 'ACCT1' account_rank, 0 alloc_amount, 0 percent_amount, 'Y' full_remainder from dual union all select 2, 2000, 534325, 'ACCT1', 0, .2, 'N' from dual union all select 2, 2000, 5466532, 'ACCT2', 100, 0, 'N' from dual union all select 2, 2000, 278567, 'ACCT3', 0, 0, 'Y' from dual union all select 3, 6000, 234666, 'ACCT1', 0, .5, 'N' from dual union all select 3, 6000, 34367, 'ACCT2', 0, 0, 'Y' from dual union all select 4, 7000, 6767685, 'ACCT1', 1111, 0, 'N' from dual union all select 4, 7000, 33453, 'ACCT2', 0, .9, 'N' from dual union all select 4, 7000, 377665, 'ACCT3', 200, 0, 'N' from dual union all select 4, 7000, 76354, 'ACCT4', 100, 0, 'N' from dual union all select 4, 7000, 9789453, 'ACCT5', 0, 0, 'Y' from dual union all select 5, 1000.01, 736453, 'ACCT1', 0, .5, 'N' from dual union all select 5, 1000.01, 4578645, 'ACCT2', 0, 0, 'Y' from dual union all select 6, 666.66, 769674, 'ACCT1', 0, .8, 'N' from dual union all select 6, 666.66, 23264, 'ACCT2', 0, .2, 'N' from dual ) select mytable.*, case when full_remainder = 'N' and alloc_amount > 0 then alloc_amount --a fixed allocation amount when full_remainder = 'N' and percent_amount > 0 then round(amount * percent_amount,2) --a percentage alloation amount when full_remainder = 'Y' then amount - sum(round(amount * percent_amount,2)) over (partition by empid) - sum(alloc_amount) over (partition by empid) end distribution_amount from mytable order by empid, full_remainder, account_rank; 

submitted by /u/p90rushb
[link] [comments]


Go to Source of this post
Author Of this post: /u/p90rushb
Title Of post: [Oracle] Help with a calculated field that involves partitioning data and running totals
Author Link: {authorlink}