This discussion is archived
7 Replies Latest reply: Feb 22, 2013 10:17 AM by chris227 RSS

calculation

750281 Newbie
Currently Being Moderated
hi i am using oracle 10g database,
 id             fdt           tdt             amt
100         01-JAN-84       30-JUN-85       10000
100         01-JUL-85       15-NOV-85       15000
 
SO, i want to find the last 12month average amount. Here less than a month will be count as a full month i.e 15-nov-85 will be taken as a full nov month.

as per the above data--
15-nov-85 to 01-jul-85 = 5 months i.e 5*15000 =75000
30-jun-05 to 01-dec-84=7 months i.e 7*10000=70000

round( (75000+70000)/12)= 12083

final output shouldl be like this
 id             fdt           tdt             amt
100         01-DEC-84       15-NOV-85       12083
 
please help.

thanks

Edited by: Hi FRNzzz!! on Feb 11, 2013 9:56 PM

Edited by: Hi FRNzzz!! on Feb 11, 2013 10:04 PM
  • 1. Re: calculation
    BluShadow Guru Moderator
    Currently Being Moderated
    Your question is not clear.

    Where is the table and data in a format we can use? (create table and insert statements?)
    What is the expected output?

    just two rows of example data isn't much for people to understand exactly what you're trying to do.

    You should know better by now

    {message:id=9360002}
  • 2. Re: calculation
    stefan nebesnak Journeyer
    Currently Being Moderated
    This works on 11g:
    .  ID   FDT       TDT         AMT
    1  100  1.7.1983  1.2.1984    10000 --"0"
    2  100  1.3.1984  25.1.1985   15000 --"2 * 15000 = 30000"
    3  100  1.2.1985  20.7.1985   250   --"6 * 250 = 1500"
    4  100  1.8.1985  15.11.1985  1250  --"4 * 1250 = 5000"
     
    --"30000 + 1500 + 5000 = 36500"
    --"36500 / 12 = 3042 (rounded)"
    Code:
    SELECT DISTINCT id, 
                    Max(fdt_) 
                      over()                 fdt_, 
                    Max(tdt_) 
                      over()                 tdt_, 
                    Round(( SUM(amt_) 
                              over() ) / 12) amt_ 
    FROM   (SELECT id, 
                   Trunc(Add_months(Min(ttdt) 
                                      over(), -diff + 1), 'MM') fdt_, 
                   Max(tdt) 
                     over()                                     tdt_, 
                   ( ( sotsum / somb ) * diff )                 amt_ 
            FROM   (SELECT t4.*, 
                           ROWNUM 
                           rn, 
                           Decode(12 - Lead(somb, 1, 0) 
                                         over ( 
                                           ORDER BY mttdt), 12, somb, 
                                                            12 - Lead(somb, 1, 0) 
                                                                   over ( 
                                                                     ORDER BY mttdt) 
                           ) diff 
                    FROM   (SELECT t3.*, 
                                   SUM(tsum) 
                                     over ( 
                                       PARTITION BY mttdt) sotsum, 
                                   SUM(mb) 
                                     over ( 
                                       PARTITION BY mttdt) somb 
                            FROM   (SELECT t2.*, 
                                           ( t2.mb * t2.amt ) tsum, 
                                           Decode(ttdt, Min(ttdt) 
                                                          over(), 0, 
                                                        1)    mttdt 
                                    FROM   (SELECT id, 
                                                   fdt, 
                                                   tdt, 
                                                   amt, 
                                                   Last_day(tdt) 
                                                   ttdt, 
                                                   Trunc(Add_months(Max(tdt) 
                                                                      over(), -12), 
                                                   'MM') 
                                                   mmth, 
                                                   Ceil(Abs(Months_between (Trunc( 
                                                            fdt), 
                                                            Last_day( 
                                                            tdt)))) mb 
                                            FROM   ( 
    -----["input table"]-----
    select 100 id, to_date('01-07-1983', 'DD-MM-YYYY') fdt, to_date('01-02-1984', 'DD-MM-YYYY') tdt, 10000 amt from dual union all
    select 100 id, to_date('01-03-1984', 'DD-MM-YYYY') fdt, to_date('25-01-1985', 'DD-MM-YYYY') tdt, 15000 amt from dual union all
    select 100 id, to_date('01-02-1985', 'DD-MM-YYYY') fdt, to_date('20-07-1985', 'DD-MM-YYYY') tdt,   250 amt from dual union all
    select 100 id, to_date('01-08-1985', 'DD-MM-YYYY') fdt, to_date('15-11-1985', 'DD-MM-YYYY') tdt,  1250 amt from dual
    -----[/"input table"]-----
                                                   ) t) t2 
                                    WHERE  ttdt >= mmth 
                                    ORDER  BY ttdt ASC) t3) t4 
                    WHERE  ROWNUM < 3) t5); 
    Result:
    .  ID   FDT_      TDT_        AMT_
    1  100  1.12.1984 15.11.1985  3042
    EXPLAIN PLAN:
    -----------------------------------------------------------------------
    | Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)|
    -----------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |      |     2 |    62 |    10  (20)|
    |   1 |  HASH UNIQUE              |      |     2 |    62 |    10  (20)|
    |   2 |   WINDOW BUFFER           |      |     2 |    62 |    10  (20)|
    |   3 |    VIEW                   |      |     2 |    62 |     9  (12)|
    |   4 |     WINDOW BUFFER         |      |     2 |   120 |     9  (12)|
    |   5 |      VIEW                 |      |     2 |   120 |     9  (12)|
    |   6 |       WINDOW BUFFER       |      |     2 |   100 |     9  (12)|
    |   7 |        COUNT STOPKEY      |      |       |       |            |
    |   8 |         VIEW              |      |     4 |   200 |     9  (12)|
    |   9 |          WINDOW SORT      |      |     4 |   200 |     9  (12)|
    |  10 |           VIEW            |      |     4 |   200 |     8   (0)|
    |  11 |            WINDOW SORT    |      |     4 |   196 |     8   (0)|
    |  12 |             VIEW          |      |     4 |   196 |     8   (0)|
    |  13 |              WINDOW BUFFER|      |     4 |    96 |     8   (0)|
    |  14 |               VIEW        |      |     4 |    96 |     8   (0)|
    |  15 |                UNION-ALL  |      |       |       |            |
     
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    |  16 |                 FAST DUAL |      |     1 |       |     2   (0)|
    |  17 |                 FAST DUAL |      |     1 |       |     2   (0)|
    |  18 |                 FAST DUAL |      |     1 |       |     2   (0)|
    |  19 |                 FAST DUAL |      |     1 |       |     2   (0)|
    -----------------------------------------------------------------------
  • 3. Re: calculation
    chris227 Guru
    Currently Being Moderated
    Something like
    with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
    select 100,to_date('01-JAN-84','DD-MON-RR'),to_date('30-JUN-85','DD-MON-RR'),10000 from dual union all
    select 100,to_date('01-JUL-85','DD-MON-RR'),to_date('15-NOV-85','DD-MON-RR'),15000 from dual union all
    select 100,to_date('01-JAN-83','DD-MON-RR'),to_date('30-NOV-83','DD-MON-RR'),10000 from dual)
    
    select
    id
    ,trunc(add_months(max(tdt),-11),'mm') fdt
    ,max(tdt) tdt
    ,round(sum(
    case when md + mb < 12 then amt * mb
    else (12 - md) * amt
    end
    )/12) amt
    from (
    select
    id,fdt,tdt,amt
    ,ceil(months_between(max(tdt) over (partition by id order by tdt desc nulls last),tdt)) md
    ,ceil(months_between(tdt,fdt)) mb
    from
    data
    )
    where
    md < 12
    group by id
    
    ID FDT TDT AMT 
    100 12/01/1984 11/15/1985 12083 
    I admit that i am not quite sure if the 12 in case when md + mb < 12 then amt * mb has to be a 13 or not. May be you will try it out with some test data yourself ;-)

    SOme explanation:
    md is the distance in months of each tdt to the max(tdt). All rows with a distance higher than 11 can be excluded.
    mb is the range of month each row covers.
    When distance and range exceed 12 the weight of the amount must be cut down to the remaining number of months to reach 12.
    Otherwise each amount is weighted with the range.

    Edited by: chris227 on 14.02.2013 02:01
    start date corrected

    Edited by: chris227 on 14.02.2013 02:03
    explanation
  • 4. Re: calculation
    750281 Newbie
    Currently Being Moderated
    first of all thanks and query is impresive
    as per you query , I CHANGED THE INPUT DATES BUT desired output calculation somewhere not getting
      with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
    select 100,to_date('01-JUL-85','DD-MON-RR'),to_date('15-JUN-86','DD-MON-RR'),3000 from dual union all
    select 100,to_date('16-JUN-86','DD-MON-RR'),to_date('15-NOV-86','DD-MON-RR'),7000 from dual)
     
    select
    id
    ,trunc(add_months(max(tdt),-11),'mm') fdt
    ,max(tdt) tdt
    ,round(sum(
    case when md + mb < 12 then amt * mb
    else (12 - md) * amt
    end
    )/12) amt
    from (
    select
    id,fdt,tdt,amt
    ,ceil(months_between(max(tdt) over (partition by id order by tdt desc nulls last),tdt)) md
    ,ceil(months_between(tdt,fdt)) mb
    from
    data
    )
    where
    md < 12
    group by id
    
    RESULT GOT
    ID     FDT      TDT     AMT 
    100 12/01/1985 11/15/1986 4667 
     
    which should be
    ID     FDT      TDT      AMT 
    100 12/01/1985 11/15/1986 4853
    because
    from 01/06/86 to 15/06/86 and 16/06/86 to 30/06/86 amount calculation should be taken as per the amount and no. of days present respectively
    thanks
  • 5. Re: calculation
    chris227 Guru
    Currently Being Moderated
    I just get 4749, how is the calculus to get 4853?
    with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
    select 200,to_date('01-JUL-85','DD-MON-RR'),to_date('15-JUN-86','DD-MON-RR'),3000 from dual union all
    select 200,to_date('16-JUN-86','DD-MON-RR'),to_date('15-NOV-86','DD-MON-RR'),7000 from dual)
    
    select
     id
    ,trunc(add_months(max(tdt),-11),'MM') fdt
    ,max(tdt) tdt
    ,round(sum(
    case when md  <= 0 then amt * mb
    else (mb - md + 1) * amt
    end
    )/sum(
    case when md  <= 0 then mb
    else (mb - md + 1)
    end)) amt
    from (
    select
     id,fdt,tdt,amt
    ,trunc(add_months(max(tdt) over (partition by id), - 11),'MM') - fdt + 1 md
    ,tdt-fdt+1 mb
    ,months_between(max(tdt) over (partition by id),tdt) mdd
    from
    data
    )
    where
    mdd <= 12
    group by id
    
    ID     FDT     TDT     AMT
    200     12/01/1985     11/15/1986     4749
    close but i need an example of the excat calculation:
    with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
    select 100,to_date('01-JAN-84','DD-MON-RR'),to_date('30-JUN-85','DD-MON-RR'),10000 from dual union all
    select 100,to_date('01-JUL-85','DD-MON-RR'),to_date('15-NOV-85','DD-MON-RR'),15000 from dual union all
    select 100,to_date('01-JAN-83','DD-MON-RR'),to_date('30-NOV-83','DD-MON-RR'),10000 from dual union all
    select 200,to_date('01-JUL-85','DD-MON-RR'),to_date('15-JUN-86','DD-MON-RR'),3000 from dual union all
    select 200,to_date('16-JUN-86','DD-MON-RR'),to_date('15-NOV-86','DD-MON-RR'),7000 from dual)
    
    select
     id
    ,trunc(add_months(max(tdt),-11),'MM') fdt
    ,max(tdt) tdt
    ,round(sum(
    case when md  <= 0 then amt * mb
    else (mb - md + 1) * amt
    end
    )/sum(
    case when md  <= 0 then mb
    else (mb - md + 1)
    end)) amt
    from (
    select
     id,tdt,amt
    ,trunc(add_months(max(tdt) over (partition by id), - 11),'MM') - fdt + 1 md
    ,trunc(add_months(tdt,1),'MM') - fdt mb
    ,months_between(trunc(add_months(max(tdt) over (partition by id), - 11),'MM'),tdt) mdd
    from
    data
    )
    where
    mdd <= 12
    group by id
    
    ID     FDT     TDT     AMT
    100     12/01/1984     11/15/1985     12096
    200     12/01/1985     11/15/1986     4768
    Edited by: chris227 on 17.02.2013 10:21
  • 6. Re: calculation
    750281 Newbie
    Currently Being Moderated
    first of all i am extremely sorry . result will come 4833.

    now how it will come as follows
    15/11/86 to 01/07/86= 7000 *5 = 35000
    30/06/86 to 16/06/86= (7000/30)*15=3500 as total days in jun is 30 , so after division multiply the amount with no. of days between 30/06/86 to 16/06/86.
    15/06/86 to 01/06/86=(3000/30)*15=1500 same as above line
    30/05/86 to 01/12/85=3000*6=18000
    
    so finally 35000+3500+1500+18000=58000/12 comes round(4833.33)=4833
    hope it will help to understand
  • 7. Re: calculation
    chris227 Guru
    Currently Being Moderated
    Sorry for the delay, i was sick and i am still, but not that worse anymore.
    May be a variation of my first approach:
    with data(id,fdt,tdt,amt) as (-- just test data, replace datain the query with the real table name
    select 100,to_date('01-JAN-84','DD-MON-RR'),to_date('30-JUN-85','DD-MON-RR'),10000 from dual union all
    select 100,to_date('01-JUL-85','DD-MON-RR'),to_date('15-NOV-85','DD-MON-RR'),15000 from dual union all
    select 100,to_date('01-JAN-83','DD-MON-RR'),to_date('30-NOV-83','DD-MON-RR'),10000 from dual union all
    select 200,to_date('01-JUL-85','DD-MON-RR'),to_date('15-JUN-86','DD-MON-RR'),3000 from dual union all
    select 200,to_date('16-JUN-86','DD-MON-RR'),to_date('15-NOV-86','DD-MON-RR'),7000 from dual)
    
    select
    id
    ,trunc(add_months(max(tdt),-11),'mm') fdt
    ,max(tdt) tdt
    ,round(sum(
    case when md + mb <= 12 then amt * 
    decode(md, .5 , round(months_between(trunc(add_months(tdt,1), 'MM'), fdt),1), mb)
    else (12 - md) * amt
    end
    )/12) amt
    from (
    select
    id,fdt,tdt,amt
    ,round(
      months_between(
        trunc(add_months(max(tdt) over (partition by id order by tdt desc nulls last),1),'MM')-1
       ,tdt-1)
     ,1) md
    ,round(months_between(tdt,fdt-1),1) mb
    from
    data
    )
    where
    md < 12
    group by id
    
    ID     FDT     TDT     AMT
    100     12/01/1984     11/15/1985     12083
    200     12/01/1985     11/15/1986     4833
    I use months_between here. This considers all months as having the same number of days.
    So the average for example over the range 01-Feb/15-Feb-16-Feb/28-Feb is calculated the same way as for the other months, although the second half of the month is shorter. But as you go for a monthly average this seems to be the correct way.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points