Generate Aggregated Rolling last six quarter results per sector per quarter.
Output : Quarter , Current Quarter Result , Aggregated Last Six quarter Results.
-- Create a table for test example
create table sector_quarter_results
(quarter varchar2(20),
sector varchar2(20),
results number);
-- Financial sector
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',100000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',90000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',80000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',70000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',60000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',50000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',40000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',30000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',20000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',10000,'Financial');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',5000,'Financial');
-- Healthcare sector
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',1000,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',900,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',800,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',700,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',600,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',500,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',400,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',300,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',200,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',100,'Healthcare');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',50,'Healthcare');
-- Insurance Sector
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2010',10000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2010',9000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2010',8000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2009',7000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2009',6000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2009',5000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2009',4000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q4 2008',3000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q3 2008',2000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q2 2008',1000,'Insurance');
insert into sector_quarter_results (quarter,results,sector) values ('Q1 2008',500,'Insurance');
commit;
Reporting Query:
select quarter,sector,current_results,
(nvl(current_results,0)+
nvl(previous_quarter_1,0)+
nvl(previous_quarter_2,0)+
nvl(previous_quarter_3,0)+
nvl(previous_quarter_4,0)+
nvl(previous_quarter_5,0)) rolling_6_quarters
from
(
select quarter,
sector,
results current_results,
lag(results,1) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_1,
lag(results,2) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_2,
lag(results,3) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_3,
lag(results,4) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_4,
lag(results,5) over (partition by sector order by substr(quarter,4)||substr(quarter,2,1)) previous_quarter_5
from sector_quarter_results
)
order by sector asc,(substr(quarter,4)||substr(quarter,2,1)) desc;
OUTPUT:
QUARTER SECTOR CURRENT_RESULTS ROLLING_6_QUARTERS
---------------- ------------------ --------------- ------------------
Q3 2010 Financial 100000 450000
Q2 2010 Financial 90000 390000
Q1 2010 Financial 80000 330000
Q4 2009 Financial 70000 270000
Q3 2009 Financial 60000 210000
Q2 2009 Financial 50000 155000
Q1 2009 Financial 40000 105000
Q4 2008 Financial 30000 65000
Q3 2008 Financial 20000 35000
Q2 2008 Financial 10000 15000
Q1 2008 Financial 5000 5000
Q3 2010 Healthcare 1000 4500
Q2 2010 Healthcare 900 3900
Q1 2010 Healthcare 800 3300
Q4 2009 Healthcare 700 2700
Q3 2009 Healthcare 600 2100
Q2 2009 Healthcare 500 1550
Q1 2009 Healthcare 400 1050
Q4 2008 Healthcare 300 650
Q3 2008 Healthcare 200 350
Q2 2008 Healthcare 100 150
Q1 2008 Healthcare 50 50
Q3 2010 Insurance 10000 45000
Q2 2010 Insurance 9000 39000
Q1 2010 Insurance 8000 33000
Q4 2009 Insurance 7000 27000
Q3 2009 Insurance 6000 21000
Q2 2009 Insurance 5000 15500
Q1 2009 Insurance 4000 10500
Q4 2008 Insurance 3000 6500
Q3 2008 Insurance 2000 3500
Q2 2008 Insurance 1000 1500
Q1 2008 Insurance 500 500
No comments:
Post a Comment