Select data with missing time periods

[barnard90]

I have a table (PROJECT) which maintains yearly expenses information. It has the expenses for an year and also the cumulative expenses till that year .

The table may or may not have data for all the years in a consecutive manner


The PROJECT table data is like


Project_id Year Yr_Exp Cum_Exp
---------------------------------------

ABC 2002 1000 1000
ABC 2005 2000 3000
ABC 2007 7000 10000

But I need a view which gives me data for all the consecutive years whether or not any expenses have been incurred in that year
I need an output like below

Project_id Year Yr_Exp Cum_Exp
---------------------------------------

ABC 2002 1000 1000
ABC 2003 0 1000
ABC 2004 0 1000
ABC 2005 2000 3000
ABC 2006 0 3000
ABC 2007 7000 10000

How can I achieve this . Could someone please suggest me a query for this

 

[dbtoo2001]

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2078828

 

[taupirho]

One way would be to create a little driver table. This would contain two columns, the project_id and year.
It would be populated with all distinct years and project ids that you wish to report on, so in your example it would look like


ID YR
=============
ABC 2002
ABC 2003
ABC 2004
ABC 2005
ABC 2006
ABC 2007

Next, run this somewhat complicated query
(note table t1 corresponds to your project table and t2 is the driver table)

select id,yr,yr_exp,max(cum_exp) over(partition by max_rn order by id,yr)
from
(
select id,yr,yr_exp,cum_exp,next_cum_exp,
max(rn) over (order by id,yr) max_rn
from
(
select id,yr,yr_exp,cum_exp,next_cum_exp,
case when cum_exp is not null or cum_exp <> next_cum_exp then rn end rn
from
(
select id,yr,yr_exp,cum_exp,
lead(cum_exp) over (order by id,yr) next_cum_exp,
row_number() over (order by id,yr) rn
from
(
select distinct
id,
yr,
sum(yr_exp) over (partition by id,yr) yr_exp,
sum(cum_exp) over (partition by id,yr) cum_exp
from
(
select id,yr,yr_exp,cum_exp
from t1
union
select t2.id,t2.yr,0,null
from t2, t1
where t2.id = t1.id
)
)
)
)
order by 1,2



ID YR YR_EXP MAX(CUM_EXP)OVER(PARTITIONBYMAX_RNORDERBYID,YR)
--- ---------- ---------- -----------------------------------------------
ABC 2002 1000 1000
ABC 2003 0 1000
ABC 2004 0 1000
ABC 2005 2000 3000
ABC 2006 0 3000
ABC 2007 7000 10000



In order to understand recursion, you must first understand recursion.

 

[taupirho]

or ....
with the much more simple query

select
id,yr,xx,
sum(xx)
over (partition by id order by id,yr range unbounded preceding) yy
from
(
select distinct
id,yr,sum(yr_exp) over(partition by id,yr) xx
from
(
select id,yr,yr_exp,cum_exp
from t1
union
select t2.id,t2.yr,0,null
from  t2, t1
where t2.id = t1.id
)
)

ID YR XX YY
--- ---------- ---------- ----------
ABC 2002 1000 1000
ABC 2003 0 1000
ABC 2004 0 1000
ABC 2005 2000 3000
ABC 2006 0 3000
ABC 2007 7000 10000



In order to understand recursion, you must first understand recursion.