create table debt (
date_rest date,
rest number(8,2)
);
insert into debt(date_rest, rest)
values(to_date('12.01.2010', 'dd.mm.yyyy'), 100);
insert into debt(date_rest, rest)
values(to_date('10.02.2010', 'dd.mm.yyyy'), 800);
insert into debt(date_rest, rest)
values(to_date('15.04.2010', 'dd.mm.yyyy'), 0);
insert into debt(date_rest, rest)
values(to_date('11.09.2010', 'dd.mm.yyyy'), 400);
insert into debt(date_rest, rest)
values(to_date('01.06.2010', 'dd.mm.yyyy'), 0);
insert into debt(date_rest, rest)
values(to_date('01.07.2010', 'dd.mm.yyyy'), 0);
insert into debt(date_rest, rest)
values(to_date('12.07.2010', 'dd.mm.yyyy'), 300);
with debt2 as
(select dt.*,
lag(date_rest,1) over (order by date_rest) prev_date_rest,
lag(rest,1) over (order by date_rest) prev_rest
from
(select date_rest, rest, last_day(date_rest) last_day_debt,
ROW_NUMBER() OVER (PARTITION BY last_day(date_rest) ORDER BY date_rest desc) rn
from debt) dt
where rn = 1),
lastdaymonth as (
select last_day(add_months (trunc (to_date('30.11.2009', 'dd.mm.yyyy'), 'mm'), + level)) last_day_month
from dual
connect by level <= 9
),
d as (
select ld.last_day_month, d2.* from
lastdaymonth ld left join debt2 d2
on ld.last_day_month = d2.last_day_debt
order by last_day_month)
select
d.*,
case
when rest = 0 then 0
when rest <> 0 and nvl(lag(rest) over (order by last_day_month), 0) = 0 then last_day_month - date_rest
when rest <> 0 and lag(rest) over (order by last_day_month) <> 0 then last_day_month - prev_date_rest
when rest is null and lag(rest) over (order by last_day_month) = 0 then 0
when rest is null and lag(rest) over (order by last_day_month) <> 0
and lag(prev_rest,1) over (order by last_day_month) = 0
then last_day_month - lag(date_rest) over (order by last_day_month)
when rest is null and lag(rest,1) over (order by last_day_month) <> 0
and lag(prev_rest,1) over (order by last_day_month) <> 0
then last_day_month - lag(prev_date_rest,1) over (order by last_day_month)
end as period
--lag(rest,1) over (order by last_day_month) lag_rest,
--lag(prev_rest,1) over (order by last_day_month) lag_prev_rest,
--lag(prev_date_rest,1) over (order by last_day_month) lag_prev_date_rest
from d
--where date_rest is null
order by last_day_month
Telegram: DB developers channel