CREATE TABLE debt(
date_rest date,
rest_458 NUMBER(8,2)
);
INSERT INTO debt(date_rest, rest_458)
values(to_date( '12.01.2010', 'dd.mm.yyyy'), 100);
INSERT INTO debt(date_rest, rest_458)
values(to_date( '10.02.2010', 'dd.mm.yyyy'), 800);
INSERT INTO debt(date_rest, rest_458)
values(to_date( '15.04.2010', 'dd.mm.yyyy'), 0);
INSERT INTO debt(date_rest, rest_458)
values(to_date( '11.09.2010', 'dd.mm.yyyy'), 400);
INSERT INTO debt(date_rest, rest_458)
values(to_date( '01.06.2010', 'dd.mm.yyyy'), 0);
INSERT INTO debt(date_rest, rest_458)
values(to_date( '01.07.2010', 'dd.mm.yyyy'), 0);
INSERT INTO debt(date_rest, rest_458)
values(to_date( '12.07.2010', 'dd.mm.yyyy'), 300);
with last_dates as (
SELECT LAST_DAY(add_months(to_date('30/11/2009','DD/MM/YYYY'), level) ) last_day_month
from dual
CONNECT BY LEVEL <= 9
),
dbt as (
select date_rest, rest_458, last_day(date_rest) last_day_dbt,
nvl(lag(rest_458) over (order by date_rest), 0) prev_rest
, lag(date_rest) over (order by date_rest) prev_date_rest
from debt)
, d as (
select ld.*, dbt.rest_458, dbt.date_rest, dbt.prev_rest, dbt.prev_date_rest
from last_dates ld
left join dbt
on last_day_month = last_day_dbt
--order by last_day_month
),
d2 as (
select last_day_month,
case
when rest_458 is null then
lag(rest_458) over (order by last_day_month)
else rest_458
end rest_458,
case
when date_rest is null then
lag(date_rest) over (order by last_day_month)
else date_rest
end date_rest,
case
when prev_rest is null then
lag(prev_rest) over (order by last_day_month)
else prev_rest
end prev_rest,
case
when prev_date_rest is null then
lag(prev_date_rest) over (order by last_day_month)
else prev_date_rest
end prev_date_rest
from d
)
select d2.*,
case
when rest_458 = 0 then 0
when rest_458 <> 0 and prev_rest = 0 then
last_day_month - date_rest
when rest_458 <> 0 and prev_rest <> 0 then
last_day_month - prev_date_rest
-- when rest_458 is null and lag(rest_458) over (order by last_day_month) = 0 then 0
-- when rest_458 is null and lag(rest_458) over (order by last_day_month) <> 0 then 999
-- ld.last_day_month - (lag(last_day_month) over (order by last_day_month))
else 10000
end rest_day
from d2
order by last_day_month
select ld.*, dbt.*,
case
when rest_458 = 0 then 0
when rest_458 <> 0 and prev_rest = 0 then
ld.last_day_month - dbt.date_rest
when rest_458 <> 0 and prev_rest <> 0 then
ld.last_day_month - dbt.prev_date_rest
when rest_458 is null and lag(rest_458) over (order by last_day_month) = 0 then 0
when rest_458 is null and lag(rest_458) over (order by last_day_month) <> 0 then 999
ld.last_day_month - (lag(last_day_month) over (order by last_day_month))
else 10000
end rest_day
from last_dates ld
left join dbt
on last_day_month = last_day_dbt
order by last_day_month
Telegram: DB developers channel