Finding total deductions based on sum of prior rows in SQL

Question

I've got a rather tricky problem that I've been trying to solve for the past few days. I am currently solving it with the Oracle SQL Model clause and could probably have written a function but I'm looking for a nice simple solution using analytic functions or something but can't figure anything out.

For a given policy (ddpsid), I want to sum up the deductions column (ddddpc). [Sorry about the difficult column names, they are not mine]. Sounds simple, but if the ddbnep column is 'Y' then I want to sum up all the prior deductions and take the current deduction as a percentage of what has already been deducted. So, if the current deduction is 10% and the prior deductions are 20% (i.e., there is 80% remaining), then I want to deduct 8% (10% or 80%), for a total of 28%.

The code below is what I am currently using:

with my_sample_data as (
  select 1 as ddpsid, ddddsq, ddddpc, ddbnep, ddadep
  from (
    select 1 as ddddsq, 10 as ddddpc, 'N' as ddbnep, 'Y' as ddadep from dual union all
    select 2 as ddddsq, 10 as ddddpc, 'Y' as ddbnep, 'Y' as ddadep from dual union all
    select 3 as ddddsq, 10 as ddddpc, 'N' as ddbnep, 'Y' as ddadep from dual union all
    select 4 as ddddsq, 10 as ddddpc, 'Y' as ddbnep, 'Y' as ddadep from dual
  )
)
--        select
--            ddpsid,
--            cumul as ddddpc
--        from (
          select
            ddpsid,
            ddddsq,
            ddadep,
            ddbnep,
            ddddpc,
            rn,
            num_rows,
            100 * (1-cumul) as cumul
          from my_sample_data a
          where ddadep = 'Y'
          model
          return all rows
          partition by (ddpsid)
          dimension by (row_number() over(partition by ddpsid order by ddddsq) as rn)
          measures (ddddsq, ddadep, ddddpc, ddbnep, 0 as cumul,
                        count(*) over(partition by ddpsid) as num_rows)
          rules automatic order (
            cumul[rn] = case
                          when nvl(ddbnep[cv(rn)],'N') = 'N'
                            then nvl(cumul[cv(rn)-1],1)- ddddpc[cv(rn)] /100
                          else nvl(cumul[cv(rn)-1],1)* (1- ddddpc[cv(rn)]/100) end
          )
--        )
--        where rn = num_rows

The data is to be grouped by ddpsid, and processed in order of ddddsq. The combination of ddpsid and ddddsq should be unique. The deduction percentage is in the ddddpc column. I only want to process rows where ddadep = 'Y'. And finally, if the ddbnep column = 'N' then I want to just add ddddpc to the running total, otherwise if ddbnep = 'Y' I want to take ddddpc as a percentage of (100% - the running total) and add it to the running total.

The code commented out is necessary because I really only want the last value for each ddpsid but it shows the working a bit better without that.

Sorry about the long question but it is about the most concise description I can provide.

The code above shows four deductions, two normal ones and two net of prior.

• The first at 10% is normal and gives a running total of 10%.
• The second at 10% is net of prior. The prior deductions summed to 10% so there is a remaining amount of 90%. So this deduction should be 9%, giving a running total of 19%
• The third at 10% is normal and gives a running total of 29%.
• The final at 10% is also net of prior. The prior deductions summed to 29% so there is a remaining amount of 71%. So this deduction should be 7.1%, giving a running total of 36.1%

After trying for two or three days to try and find a SQL solution to this, I'm a little disappointed that I couldn't and am hoping I didn't miss anything.

So, is there any way to rewrite this without using the model clause and without writing a function?

Answer 1

Mikey,

For your query, you need to calculate values based on previously calculated values. This is a type of operation that can only be effectively done using the SQL Model Clause or with the Recursive Subquery Factoring. The latter was introduced in version 11g Release 2. You can read about it here in the documentation and here in a blogpost of mine. Since I don't know what version you are on, I don't know how useful this suggestion is.

But why do you want to rewrite it without using the model clause?

By the way, you can simplify your query somewhat if you use this variant:

SQL> with my_sample_data as
  2  ( select 1 as ddpsid, ddddsq, ddddpc, ddbnep, ddadep
  3      from ( select 1 as ddddsq, 10 as ddddpc, 'N' as ddbnep, 'Y' as ddadep from dual union all
  4             select 2 as ddddsq, 10 as ddddpc, 'Y' as ddbnep, 'Y' as ddadep from dual union all
  5             select 3 as ddddsq, 10 as ddddpc, 'N' as ddbnep, 'Y' as ddadep from dual union all
  6             select 4 as ddddsq, 10 as ddddpc, 'Y' as ddbnep, 'Y' as ddadep from dual
  7           )
  8  )
  9  select ddpsid
 10       , ddddsq
 11       , 'Y' as ddadep
 12       , ddbnep
 13       , ddddpc
 14       , rn
 15       , num_rows
 16       , cumul
 17    from my_sample_data a
 18   where ddadep = 'Y'
 19   model
 20         partition by (ddpsid, count(*) over (partition by ddpsid) as num_rows)
 21         dimension by (row_number() over (partition by ddpsid order by ddddsq) as rn)
 22         measures (ddddsq, ddddpc, ddbnep, 0 as cumul)
 23         ( cumul[any] order by rn
 24           = case nvl(ddbnep[cv()],'N')
 25             when 'N' then
 26               nvl(cumul[cv()-1],0) + ddddpc[cv()]
 27             when 'Y' then
 28               100 - ((100 - nvl(cumul[cv()-1],0)) * (1-ddddpc[cv()]/100))
 29             end
 30         )
 31  /

    DDPSID     DDDDSQ D D     DDDDPC         RN   NUM_ROWS      CUMUL
---------- ---------- - - ---------- ---------- ---------- ----------
         1          1 Y N         10          1          4         10
         1          2 Y Y         10          2          4         19
         1          3 Y N         10          3          4         29
         1          4 Y Y         10          4          4       36.1

4 rows selected.

Hope this helps.

Regards, Rob.