Forum Stats

  • 3,769,310 Users
  • 2,252,947 Discussions
  • 7,874,983 Comments

Discussions

calculate billamount based on the conditions mentioned in the sheet

Siva  ManU
Siva ManU Member Posts: 14 Red Ribbon

we have to find the billvalue field based on conditions mentioned in the excel screenshot ,so i wrote a below query ,but i need to know the other better approach. with main_table as (

select

1 planid , 'planA' planname ,'10000,0.25;10000,0.20;10000,0.15;99999,0.10' usagerate, 33000 tot_usage

from dual

union

select 2 planid , 'planB' planname ,'5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10' usagerate, 33000 tot_usage

from dual

union

select

3 planid , 'planC' planname ,'5000,0.25;10000,0.20;99999,0.15' usagerate, 33000 tot_usage

from dual

union

select

4 planid , 'planD' planname ,'99999,0.25' usagerate, 33000 tot_usage

from dual

union

select

5 planid , 'planE' planname ,'25000,0.25;99999,0.20' usagerate, 33000 tot_usage

from dual

),

sub_query1 as (

select a.*,

level as lvl,

regexp_substr(usagerate,'[^;]+', 1, level) ind_usage_rate

from main_table a

connect by regexp_substr(usagerate,'[^;]+', 1, level) is not null),

sub_query2 as (

select distinct b.* ,

substr(ind_usage_rate,1,instr(ind_usage_rate,',')-1) usage,

substr(ind_usage_rate,instr(ind_usage_rate,',')+1) rate

from sub_query1 b),

sub_query3 as (

select planid,planname,tot_usage,usagerate,lvl,usage,rate,

decode (usage,'99999',0,usage*rate) as used

from sub_query2

)

--select distinct a.* from sub_query3 a order by planid,lvl

,

sub_query4 as (

select distinct c.*,

sum(usage) over(partition by planid order by lvl) sum_usage,

sum(usage*rate) over(partition by planid order by lvl) sum_rate

from sub_query3 c

)

--select * from sub_query4 order by planid,lvl

,

sub_query5 as (

select d.* ,

case when sum_usage >tot_usage then

  tot_usage-lag(sum_usage,1,0) over(partition by planid order by lvl)

  else 0

  end as remaining_usage

from sub_query4 d

)

--select * from sub_query5 order by planid,lvl

,

sub_query6 as (

select e.* ,

case when remaining_usage>0 then

  remaining_usage*rate+lag(sum_rate,1,0) over(partition by planid order by lvl)

  end as total_amount

from sub_query5 e

)

select planid,planname,tot_usage,usagerate,total_amount from sub_query6

where total_amount is not null

Tagged:

Best Answers

  • User_H3J7U
    User_H3J7U Member Posts: 672 Silver Trophy
    Accepted Answer
    with t(id, name, tot_usg, usage_rate) as (
          select 1, 'planA', 33000, '10000,0.25;10000,0.20;10000,0.15;99999,0.10' from dual union all
          select 2, 'planB', 33000, '5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10' from dual union all
          select 3, 'planC', 33000, '5000,0.25;10000,0.20;99999,0.15' from dual union all
          select 4, 'planD', 33000, '99999,0.25' from dual union all
          select 5, 'planE', 33000, '25000,0.25;99999,0.20' from dual
          )
    select id, name, tot_usg, usage_rate, biam
    from (
      select * from t model
      partition by (id, name) dimension by (0 r)
      measures(tot_usg, usage_rate, 0 am, 0 ra, 0 biam, 0 suma)
      rules iterate (99) until (regexp_substr(usage_rate[0],'[^;]+',1,iteration_number+2) is null) (
        am[iteration_number] = regexp_substr(regexp_substr(usage_rate[0],'[^;]+',1,iteration_number+1), '[^,]+', 1, 1),
        ra[iteration_number] = regexp_substr(regexp_substr(usage_rate[0],'[^;]+',1,iteration_number+1), '[^,]+', 1, 2),
        suma[iteration_number] = greatest(0,least(am[cv()], tot_usg[0]-(sum(am)[any]-am[cv()]))),
        biam[0] = sum(suma*ra)[any]
      )
    )
    where r=0;
    
            ID NAME     TOT_USG USAGE_RATE                                                  BIAM
    ---------- ----- ---------- ----------------------------------------------------- ----------
             1 planA      33000 10000,0.25;10000,0.20;10000,0.15;99999,0.10                 6300
             2 planB      33000 5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10       7450
             3 planC      33000 5000,0.25;10000,0.20;99999,0.15                             5950
             5 planE      33000 25000,0.25;99999,0.20                                       7850
             4 planD      33000 99999,0.25                                                  8250
    
    Siva  ManU
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,916 Red Diamond
    Accepted Answer
    with sample_data(id, name, tot_usg, usage_rate)
      as (
          select 1, 'planA', 33000, '10000,0.25;10000,0.20;10000,0.15;99999,0.10' from dual union all
          select 2, 'planB', 33000, '5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10' from dual union all
          select 3, 'planC', 33000, '5000,0.25;10000,0.20;99999,0.15' from dual union all
          select 4, 'planD', 33000, '99999,0.25' from dual union all
          select 5, 'planE', 33000, '25000,0.25;99999,0.20' from dual
         ),
    t as (
          select  id,
                  name,
                  tot_usg,
                  usage_rate,
                  rate,
                  case
                    when sum(amt) over(partition by id order by lvl) <= tot_usg then amt
                    else tot_usg - sum(amt) over(partition by id order by lvl) + amt
                  end amt
            from  sample_data,
                  lateral(
                          select  level lvl,
                                  to_number(regexp_substr(usage_rate,'[^,;]+',1,2 * level - 1)) amt,
                                  to_number(regexp_substr(usage_rate,'[^,;]+',1,2 * level)) rate
                            from  dual
                            connect by level <= regexp_count(usage_rate,';') + 1
                         )
         )
    select  id,
            name,
            tot_usg,
            usage_rate,
            sum(amt * rate) bill_amt
      from  t
      where amt > 0
      group by id,
               name,
               tot_usg,
               usage_rate
      order by id
    /
    
            ID NAME     TOT_USG USAGE_RATE                                              BILL_AMT
    ---------- ----- ---------- ----------------------------------------------------- ----------
             1 planA      33000 10000,0.25;10000,0.20;10000,0.15;99999,0.10                 6300
             2 planB      33000 5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10       7450
             3 planC      33000 5000,0.25;10000,0.20;99999,0.15                             5950
             4 planD      33000 99999,0.25                                                  8250
             5 planE      33000 25000,0.25;99999,0.20                                       7850
    
    SQL>
    

    SY.

    Siva  ManU
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi, @Siva ManU

    we have to find the billvalue field based on conditions mentioned in the excel screenshot 

    Please post your requirements and the exact results you want from the given sample data right in this space. Don't use attachments.

    Always give your full Oracle version number (e.g. 18.4.0.0.0).

  • Siva  ManU
    Siva ManU Member Posts: 14 Red Ribbon
    edited Oct 11, 2021 11:14AM

    Hi Frank ,

    Thanks for your advice , i just want to learn different ways to solve this problem .I tried the above solution in 11g.

    oracle version doesn't a matter for me . The above solution works from 11g to 21c

    Oracle version 11.2.0.4.0 - 64bit

    Problem:

    i have a table like below

    id , name ,tot_usg, usage_rate    

    1   planA   33000   10000,0.25;10000,0.20;10000,0.15;99999,0.10

    2   planB   33000   5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10

    3   planC   33000   5000,0.25;10000,0.20;99999,0.15

    4   planD   33000   99999,0.25

    5   planE   33000   25000,0.25;99999,0.20


    i have to find out billamount based on usage and rates like below

    example for planA 

    tot_usage=33000

    tot_usage 33000 =(10000+10000+10000+3000) based on usage_rate

    calculation should be like

    10000*0.25+10000*0.20+10000*0.15+3000*0.1 = 6300

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,223 Red Diamond

    Hi,

    Sorry, I don't understand.

    i have a table like below

    id , name ,tot_usg, usage_rate    

    1   planA   33000   10000,0.25;10000,0.20;10000,0.15;99999,0.10

    ...

    tot_usage 33000 =(10000+10000+10000+3000) based on usage_rate

    Why isn't tot_usage = 10000 + 10000 + 10000 + 99999 = 129999?

    You've probably figured this out by now, but storing several data items in a single string as a delimited list is not a good idea. Relational databases work best when every column of every row contains (at most) one piece of information. This is so basic to table design that it's call First Normal Form. Literally breaking the first rule of table design is simply asking for trouble.

    example for planA 

    Always post the complete results you want from the given sample data (including planB, planC, ...)

  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond

    As Frank indicates, it's not clear where you are getting the "3000" value from in the data?

    If I assume (perhaps wrongly) that the 3000 comes from the (10000+10000+10000)*0.1, (so effectively those values, except the 99999 value added together and then multiplied by the 0.1 from the 99999 value)...

    SQL> ed
    Wrote file afiedt.buf
    
      1  with t(id, name, tot_usg, usage_rate) as (
      2    select 1, 'planA', 33000, '10000,0.25;10000,0.20;10000,0.15;99999,0.10' from dual union all
      3    select 2, 'planB', 33000, '5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10' from dual union all
      4    select 3, 'planC', 33000, '5000,0.25;10000,0.20;99999,0.15' from dual union all
      5    select 4, 'planD', 33000, '99999,0.25' from dual union all
      6    select 5, 'planE', 33000, '25000,0.25;99999,0.20' from dual
      7    )
      8    ,a as (
      9      select id
     10            ,name
     11            ,tot_usg
     12            ,usage_rate
     13            ,'('||replace(regexp_replace(t.usage_rate,',.+?;','+'),'99999,','0)*') as val
     14      from   t
     15      )
     16  select id
     17        ,name
     18        ,tot_usg
     19        ,usage_rate
     20        ,to_number(y.column_value) calc_tot_usage
     21        ,to_number(z.column_value) as billvalue
     22  from  a
     23        cross join xmltable(val) x
     24        cross join xmltable(replace(regexp_replace(usage_rate,',.+?(;|$)','+'),'99999+',x.column_value)) y
     25*       cross join xmltable(replace(replace(replace(usage_rate,'99999',x.column_value),',','*'),';','+')) z
    SQL> /
    
    
            ID NAME     TOT_USG USAGE_RATE                                            CALC_TOT_USAGE  BILLVALUE
    ---------- ----- ---------- ----------------------------------------------------- -------------- ----------
             1 planA      33000 10000,0.25;10000,0.20;10000,0.15;99999,0.10                    33000       6300
             2 planB      33000 5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10          60500      11300
             3 planC      33000 5000,0.25;10000,0.20;99999,0.15                                17250     3587.5
             4 planD      33000 99999,0.25                                                         0          0
             5 planE      33000 25000,0.25;99999,0.20                                          30000       7250
    

    .. that works ok from planA... but you haven't told us what your expected output is for the other plans... so it's all just guesswork.

  • Siva  ManU
    Siva ManU Member Posts: 14 Red Ribbon
    edited Oct 11, 2021 12:36PM

    Hi Frank ,

    this is the expected o/p.

    1   planA   33000   10000,0.25;10000,0.20;10000,0.15;99999,0.10   6300

    2   planB   33000   5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10   7450

    3   planC   33000   5000,0.25;10000,0.20;99999,0.15   5950

    4   planD   33000   99999,0.25   8250

    5   planE   33000   25000,0.25;99999,0.20   7850


    there is some misunderstanding i think

    for planA total usage is 33000.

    in that for first 10000 rate is 0.25 ,

    next 10000 rate is 0.20 ,

    next 10000 rate 0.15 .

    here calculation is (10000 * 0.25+10000 *0.20+10000 * 0.15).

    out of 33000 usage , we calculated 30000 ,so for remaining 3000 we have to use 0.10.

    so final bill for planA is (10000 * 0.25+10000 *0.20+10000 * 0.15+3000 * 0.10).

  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond

    Still doesn't make sense.

    Plan B...

    You have 5000 + 10000 + 15000 + 25000 = 55000, so that exceeds the total usage of 33000

  • BluShadow
    BluShadow Member, Moderator Posts: 41,486 Red Diamond

    All but planB...

    SQL> ed
    Wrote file afiedt.buf
    
      1  with t(id, name, tot_usg, usage_rate) as (
      2    select 1, 'planA', 33000, '10000,0.25;10000,0.20;10000,0.15;99999,0.10' from dual union all
      3    select 2, 'planB', 33000, '5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10' from dual union all
      4    select 3, 'planC', 33000, '5000,0.25;10000,0.20;99999,0.15' from dual union all
      5    select 4, 'planD', 33000, '99999,0.25' from dual union all
      6    select 5, 'planE', 33000, '25000,0.25;99999,0.20' from dual
      7    )
      8    ,a as (
      9      select id
     10            ,name
     11            ,tot_usg
     12            --,usage_rate
     13            ,tot_usg-to_number(x.column_value) as usage_diff
     14            ,replace(t.usage_rate,'99999',to_char(tot_usg-to_number(x.column_value))) as billsum
     15            ,to_number(y.column_value) as billvalue
     16      from   t
     17             cross join xmltable(replace(regexp_replace(usage_rate,'99999.+$|[^,]+?;'),',','+')||'0') x
     18             cross join xmltable(replace(replace(replace(t.usage_rate,'99999',to_char(tot_usg-to_number(x.column_value))),',','*'),';','+')) y
     19      )
     20* select * from a
    SQL> /
    
            ID NAME     TOT_USG USAGE_DIFF BILLSUM                                                       BILLVALUE
    ---------- ----- ---------- ---------- ------------------------------------------------------------ ----------
             1 planA      33000       3000 10000,0.25;10000,0.20;10000,0.15;3000,0.10                         6300
             2 planB      33000     -22000 5000,0.30;10000,0.25;15000,0.20;25000,0.15;-22000,0.10             8550
             3 planC      33000      18000 5000,0.25;10000,0.20;18000,0.15                                    5950
             4 planD      33000      33000 33000,0.25                                                         8250
             5 planE      33000       8000 25000,0.25;8000,0.20                                               7850
    
    
    


  • User_H3J7U
    User_H3J7U Member Posts: 672 Silver Trophy
    Accepted Answer
    with t(id, name, tot_usg, usage_rate) as (
          select 1, 'planA', 33000, '10000,0.25;10000,0.20;10000,0.15;99999,0.10' from dual union all
          select 2, 'planB', 33000, '5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10' from dual union all
          select 3, 'planC', 33000, '5000,0.25;10000,0.20;99999,0.15' from dual union all
          select 4, 'planD', 33000, '99999,0.25' from dual union all
          select 5, 'planE', 33000, '25000,0.25;99999,0.20' from dual
          )
    select id, name, tot_usg, usage_rate, biam
    from (
      select * from t model
      partition by (id, name) dimension by (0 r)
      measures(tot_usg, usage_rate, 0 am, 0 ra, 0 biam, 0 suma)
      rules iterate (99) until (regexp_substr(usage_rate[0],'[^;]+',1,iteration_number+2) is null) (
        am[iteration_number] = regexp_substr(regexp_substr(usage_rate[0],'[^;]+',1,iteration_number+1), '[^,]+', 1, 1),
        ra[iteration_number] = regexp_substr(regexp_substr(usage_rate[0],'[^;]+',1,iteration_number+1), '[^,]+', 1, 2),
        suma[iteration_number] = greatest(0,least(am[cv()], tot_usg[0]-(sum(am)[any]-am[cv()]))),
        biam[0] = sum(suma*ra)[any]
      )
    )
    where r=0;
    
            ID NAME     TOT_USG USAGE_RATE                                                  BIAM
    ---------- ----- ---------- ----------------------------------------------------- ----------
             1 planA      33000 10000,0.25;10000,0.20;10000,0.15;99999,0.10                 6300
             2 planB      33000 5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10       7450
             3 planC      33000 5000,0.25;10000,0.20;99999,0.15                             5950
             5 planE      33000 25000,0.25;99999,0.20                                       7850
             4 planD      33000 99999,0.25                                                  8250
    
    Siva  ManU
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,916 Red Diamond
    Accepted Answer
    with sample_data(id, name, tot_usg, usage_rate)
      as (
          select 1, 'planA', 33000, '10000,0.25;10000,0.20;10000,0.15;99999,0.10' from dual union all
          select 2, 'planB', 33000, '5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10' from dual union all
          select 3, 'planC', 33000, '5000,0.25;10000,0.20;99999,0.15' from dual union all
          select 4, 'planD', 33000, '99999,0.25' from dual union all
          select 5, 'planE', 33000, '25000,0.25;99999,0.20' from dual
         ),
    t as (
          select  id,
                  name,
                  tot_usg,
                  usage_rate,
                  rate,
                  case
                    when sum(amt) over(partition by id order by lvl) <= tot_usg then amt
                    else tot_usg - sum(amt) over(partition by id order by lvl) + amt
                  end amt
            from  sample_data,
                  lateral(
                          select  level lvl,
                                  to_number(regexp_substr(usage_rate,'[^,;]+',1,2 * level - 1)) amt,
                                  to_number(regexp_substr(usage_rate,'[^,;]+',1,2 * level)) rate
                            from  dual
                            connect by level <= regexp_count(usage_rate,';') + 1
                         )
         )
    select  id,
            name,
            tot_usg,
            usage_rate,
            sum(amt * rate) bill_amt
      from  t
      where amt > 0
      group by id,
               name,
               tot_usg,
               usage_rate
      order by id
    /
    
            ID NAME     TOT_USG USAGE_RATE                                              BILL_AMT
    ---------- ----- ---------- ----------------------------------------------------- ----------
             1 planA      33000 10000,0.25;10000,0.20;10000,0.15;99999,0.10                 6300
             2 planB      33000 5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10       7450
             3 planC      33000 5000,0.25;10000,0.20;99999,0.15                             5950
             4 planD      33000 99999,0.25                                                  8250
             5 planE      33000 25000,0.25;99999,0.20                                       7850
    
    SQL>
    

    SY.

    Siva  ManU
  • EdStevens
    EdStevens Member Posts: 28,525 Gold Crown

    A couple of side observations:

    1) many sites block links. And many participants in these forums refuse to open them for the same reasons others block them. Nothing prevents you from pasting content directly into your postings here.

    2) posted code is MUCH easier to read - and comprehend, and therefore more conducive to receiveing a reply - when it is formatted as such. Please read https://community.oracle.com/tech/apps-infra/kb/articles/12-format-text

    Siva  ManU