Divide data from other days by data from one particular day

Question

I am a bit stuck on one problem for a few hours now.

Let`s say I have a table with the following data:

month          outstanding
01/05/2012     35 678 956
02/05/2012     33 678 956
03/05/2012     31 678 956
04/05/2012     27 678 956
05/05/2012     24 678 956

i need to get the ratio of say, day 05/05/2012 results to the first day of that month E.G. Outstanding of05/05/2012 divided by outstanding 01/05/2012 (24 678 956/35 678 956)

What function should i use? Tried doing over partition by / by result of to_char(trunc(trunc(a.date_,'MM'), 'MM'),'DD-MM-YYYY')

Didnt seem to work for me

score 4 · Accepted Answer · answered Jun 17 '12 at 14:58

create table temp (month date , outstanding number);
insert into temp values(to_date('01/05/2012','dd/mm/yyyy'),35678956);
insert into temp values(to_date('02/05/2012','dd/mm/yyyy'),33678956);
insert into temp values(to_date('03/05/2012','dd/mm/yyyy'),31678956);
insert into temp values(to_date('04/05/2012','dd/mm/yyyy'),27678956);
insert into temp values(to_date('05/05/2012','dd/mm/yyyy'),24678956);
insert into temp values(to_date('01/06/2012','dd/mm/yyyy'),44678956);
insert into temp values(to_date('02/06/2012','dd/mm/yyyy'),41678956);

The FIRST_VALUE analytic function picks the first record from the partition after doing the ORDER BY

SELECT month
      ,outstanding
      ,outstanding/(FIRST_VALUE(outstanding)
             OVER (PARTITION BY to_char(month,'mm') 
             ORDER BY month
        )) as ratio
FROM temp
ORDER BY month;

OUTPUT

MONTH     OUTSTANDING      RATIO
--------- ----------- ----------
01-MAY-12    35678956          1
02-MAY-12    33678956 .943944548
03-MAY-12    31678956 .887889096
04-MAY-12    27678956 .775778193
05-MAY-12    24678956 .691695015
01-JUN-12    44678956          1
02-JUN-12    41678956 .932854295

7 rows selected.

SQLFIDDLE link

score 1 · Answer 2 · answered Jun 17 '12 at 14:22

1

Try this:

SELECT t1.month,
       t1.outstanding / t2.outstanding o2
FROM your_table t1
INNER JOIN
  (SELECT *
   FROM your_table
   WHERE trunc(MONTH, 'mm') = MONTH) t2 ON trunc(t1.MONTH, 'mm') = t2.MONTH

answered Jun 17 '12 at 14:22

A.B.Cade

16,735
1
37
53

Thanks for this, but i thought of it on my one and decided not to do this because it is too heavy for my data , i mean the select itself. Is there a way to use "smarter" move by using analytical functions? – lakhlu Jun 17 '12 at 15:33

Divide data from other days by data from one particular day

2 Answers2