saad0n87 saad0n87 - 2 months ago 7
SQL Question

Create a view on multiple row conditions

I have a Cycles table like this:

Value Name Month

10 P00006 201412
20 P00006 201501
30 P00006 201502
100 P00007 201509
200 P00007 201510
300 P00007 201511


I would like to create a view based on this table where for each Name I sum the values of the previous months.
This the expected result :

AccruedValue Name Month

10 P00006 201412
30 P00006 201501
60 P00006 201502
100 P00007 201509
300 P00007 201510
600 P00007 201511


I am not sure this can be achieved without a using a cursor so any help will be appreciated.

Answer

Test data:

create table cycles 
(value NUMBER, name VARCHAR2(250), month VARCHAR2(250));
/
INSERT INTO cycles VALUES(10  , 'P00006','201412');
INSERT INTO cycles VALUES(20  , 'P00006','201501');
INSERT INTO cycles VALUES(30  , 'P00006','201502');
INSERT INTO cycles VALUES(100 , 'P00007','201509');
INSERT INTO cycles VALUES(200 , 'P00007','201510');
INSERT INTO cycles VALUES(300 , 'P00007','201511');

View my_view:

create or replace view my_view as 
select 
   sum(value) over (partition by name order by month ) as AccruedValue ,
   name ,
   month 
from cycles;

Result:

select * from v;

10  P00006  201412
30  P00006  201501
60  P00006  201502
100 P00007  201509
300 P00007  201510
600 P00007  201511