7 Replies Latest reply: Feb 22, 2013 12:17 PM by chris227 RSS

    calculation

    750281
      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
          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
            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
              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
                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
                  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
                    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
                      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.