Hello,
I have a table with this structure
table values:
(code varchar2(10),
date_value date,
value number);
Example data :
'Code1','15/03/2017',25000
'Code1','06/06/2017',26000
'Code1','18/07/2017',29000
'Code1','21/07/2017',3000
'Code2','18/07/2017',89000
'Code1','05/10/2017',35000
I have also another table "calendar" containing the dates (from 2000 to 2999 for instances, 1 row/day)
table calendar
(cal_date date);
In order to create a graph, I'd like to have a sum by month, and all months with null values to bi filled with previous non-null month value. So for instance the result would be :
01/2017 'Code1' (null)
02/2017 'Code1' (null)
03/2017 'Code1' 25000
04/2017 'Code1' 25000
05/2017 'Code1' 25000
06/2017 'Code1' 26000
07/2017 'Code1' 31000
08/2017 'Code1' 31000
09/2017 'Code1' 31000
10/2017 'Code1' 35000
11/2017 'Code1' 35000
12/2017 'Code1' 35000
01/2017 'Code2' (null)
02/2017 'Code2' (null)
03/2017 'Code2' (null)
04/2017 'Code2' (null)
05/2017 'Code2' (null)
06/2017 'Code2' (null)
07/2017 'Code2' 89000
08/2017 'Code2' 89000
09/2017 'Code2' 89000
10/2017 'Code2' 89000
11/2017 'Code2' 89000
12/2017 'Code2' 89000
Using analytics I think that using lead/lag but which "previous line" using, since in some cases the previous value is null but the "2nd" previous isn't.
Any idea ?
There's a couple of things you need to do here:
1. Generate a row for each month and code
2. Fill down the "missing" values
For step 1, you can use a partitioned outer join. This looks like:
left join t partition by (t.column)
For every row in the inner table, this generates a new set of values for each value in the partition by column. In your example, every row in calendar appears once for each code value.
To fill down the missing values, use last_value with the ignore nulls clause. This finds the last non-null value according to the partition and order by clauses.
Altogether this looks something like:
create table t (
code varchar2(10),
date_value date,
val number
);
alter session set nls_date_format = 'dd/mm/yyyy';
insert into t values ('Code1','15/03/2017',25000);
insert into t values ('Code1','06/06/2017',26000);
insert into t values ('Code1','18/07/2017',29000);
insert into t values ('Code1','21/07/2017',3000);
insert into t values ('Code2','18/07/2017',89000);
insert into t values ('Code1','05/10/2017',35000);
with dates as (
select add_months(date'2017-01-01', level-1) dt
from dual
connect by level <= 12
), monthly_tots as (
select d.dt, t.code,
sum(t.val) sm
from dates d
left join t partition by (t.code)
on d.dt <= t.date_value
and t.date_value < add_months(d.dt, 1)
group by d.dt, t.code
)
select dt, code,
last_value(sm) ignore nulls over (
partition by code order by dt
) val
from monthly_tots;
DT CODE VAL
01/01/2017 Code1 <null>
01/02/2017 Code1 <null>
01/03/2017 Code1 25000
01/04/2017 Code1 25000
01/05/2017 Code1 25000
01/06/2017 Code1 26000
01/07/2017 Code1 32000
01/08/2017 Code1 32000
01/09/2017 Code1 32000
01/10/2017 Code1 35000
01/11/2017 Code1 35000
01/12/2017 Code1 35000
01/01/2017 Code2 <null>
01/02/2017 Code2 <null>
01/03/2017 Code2 <null>
01/04/2017 Code2 <null>
01/05/2017 Code2 <null>
01/06/2017 Code2 <null>
01/07/2017 Code2 89000
01/08/2017 Code2 89000
01/09/2017 Code2 89000
01/10/2017 Code2 89000
01/11/2017 Code2 89000
01/12/2017 Code2 89000