insert into tq84_lendings values ('Peter', 'AAA', 4, date '2021-04-14', date '2021-10-04');
insert into tq84_lendings values ('Peter', 'BBB', 22, date '2021-05-12', date '2021-11-02');
insert into tq84_lendings values ('Peter', 'CCC', 7, date '2021-09-07', date '2021-10-18');
insert into tq84_lendings values ('Peter', 'EEE', 9, date '2021-10-16', date '2021-11-15');
insert into tq84_lendings values ('Maria', 'ZZZ', 14, date '2021-06-22', date '2021-09-12');
Each record needs to be duplicated, one record for the start of the lending and the other one for the end of the lending. The unpivot clause comes in handy for exactly this kind of task:
select
whom,
period,
what,
date_,
case when period = 'start' then value_ end value_lent,
case when period = 'end' then value_ end value_back
from
tq84_lendings unpivot (
date_ for period in (
start_ as 'start',
end_ as 'end'
)
)
order by
whom,
date_
;
--
-- WHOM PERIO WHAT DATE_ VALUE_LENT VALUE_BACK
-- ---------- ----- ----- ---------- ---------- ----------
-- Maria start ZZZ 2021-06-22 14
-- Maria end ZZZ 2021-09-12 14
-- Peter start AAA 2021-04-14 4
-- Peter start BBB 2021-05-12 22
-- Peter start CCC 2021-09-07 7
-- Peter end AAA 2021-10-04 4
-- Peter start EEE 2021-10-16 9
-- Peter end CCC 2021-10-18 7
-- Peter end BBB 2021-11-02 22
-- Peter end EEE 2021-11-15 9
We want at most one record per person, month and start or end. This can be achieved with a group by:
select
whom,
period,
add_months(trunc(date_, 'mm'), 1) month_,
sum(value_) sum_value
from
tq84_lendings unpivot (
date_ for period in (
start_ as 'start',
end_ as 'end'
)
)
group by
whom,
period,
trunc(date_, 'mm')
order by
whom,
trunc(date_, 'mm')
;
--
-- WHOM PERIO MONTH_ SUM_VALUE
-- ---------- ----- ---------- ----------
-- Maria start 2021-07-01 14
-- Maria end 2021-10-01 14
-- Peter start 2021-05-01 4
-- Peter start 2021-06-01 22
-- Peter start 2021-10-01 7
-- Peter end 2021-11-01 11
-- Peter start 2021-11-01 9
-- Peter end 2021-12-01 31
When a period starts, a value must be added, when the period ends, the value must be subtracted. This is achieved with the following statement:
select
whom,
add_months(trunc(date_, 'mm'), 1) month_,
sum (
sum(case when period = 'start' then 1 else -1 end * value_)
)
over (
partition by whom
order by add_months(trunc(date_, 'mm'), 1)
) lent_value
from
tq84_lendings unpivot (
date_ for period in (
start_ as 'start',
end_ as 'end'
)
)
group by
whom,
trunc(date_, 'mm')
order by
whom,
trunc(date_, 'mm')
;
-- WHOM MONTH_ LENT_VALUE
-- ---------- ---------- ----------
-- Maria 2021-07-01 14
-- Maria 2021-10-01 0
-- Peter 2021-05-01 4
-- Peter 2021-06-01 26
-- Peter 2021-10-01 33
-- Peter 2021-11-01 31
-- Peter 2021-12-01 0
Unfortunately, the previous statement only returns records for months where a lending started or ended. We have to add the missing months and fill in missing values with previous values.
with x as (
select
whom,
add_months(trunc(date_, 'mm'), 1) month_,
sum (
sum(case when period = 'start' then 1 else -1 end * value_)
)
over (
partition by whom
order by add_months(trunc(date_, 'mm'), 1)
) lent_value
from
tq84_lendings unpivot (
date_ for period in (
start_ as 'start',
end_ as 'end'
)
)
group by
whom,
-- period,
trunc(date_, 'mm')
),
-- select * from x
whoms as (
select
whom,
min(month_) month_min,
add_months(max(month_), -1) month_max
from
x
group by
whom
),
months as (
select
add_months(date '2021-01-01', level) month_
from dual
connect by level <= 12
)
select
whoms.whom,
months.month_,
nvl(
-- Fill null-values with previous non-null value.
--(https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/select/analytic/lag-lead/fill-missing-values-with-previous-value)
x.lent_value,
lag(x.lent_value)
ignore nulls
over (
partition by whoms.whom
order by months.month_
)
) as lent_value_nn
from
whoms join
months on months.month_ between whoms.month_min and whoms.month_max
left join
x on x.whom = whoms.whom and
x.month_ = months.month_
order by
whoms.whom,
months.month_
;
--
-- WHOM MONTH_ LENT_VALUE_NN
-- ---------- ---------- -------------
-- Maria 2021-07-01 14
-- Maria 2021-08-01 14
-- Maria 2021-09-01 14
-- Peter 2021-05-01 4
-- Peter 2021-06-01 26
-- Peter 2021-07-01 26
-- Peter 2021-08-01 26
-- Peter 2021-09-01 26
-- Peter 2021-10-01 33
-- Peter 2021-11-01 31
select
whom,
to_char(month_, 'yyyy mm') month_,
lent_value
from (
select
whom,
-- what,
add_months(trunc(date_, 'mm'), 1) month_,
sum (case when period = 'start' then value_ end) value_lent,
sum (case when period = 'end' then value_ end) value_back
from
tq84_lendings unpivot (
date_ for period in (
start_ as 'start',
end_ as 'end'
)
)
group by
whom,
trunc(date_, 'mm')
)
model
partition by (whom )
dimension by (month_)
measures (
value_lent,
value_back,
cast (null as number) lent_value
)
ignore nav
rules sequential order (
lent_value[
for month_
from date '2021-01-01'
to date '2022-01-01'
increment numtoyminterval(1, 'month')
] = sum(nvl(value_lent, 0) - nvl(value_back, 0))[month_ <= cv(month_)]
)
order by
whom,
month_
;
--
-- WHOM MONTH_ LENT_VALUE
-- ---------- ------- ----------
-- Maria 2021 01
-- Maria 2021 02 0
-- Maria 2021 03 0
-- Maria 2021 04 0
-- Maria 2021 05 0
-- Maria 2021 06 0
-- Maria 2021 07 14
-- Maria 2021 08 14
-- Maria 2021 09 14
-- Maria 2021 10 0
-- Maria 2021 11 0
-- Maria 2021 12 0
-- Maria 2022 01 0
-- Peter 2021 01
-- Peter 2021 02 0
-- Peter 2021 03 0
-- Peter 2021 04 0
-- Peter 2021 05 4
-- Peter 2021 06 26
-- Peter 2021 07 26
-- Peter 2021 08 26
-- Peter 2021 09 26
-- Peter 2021 10 33
-- Peter 2021 11 31
-- Peter 2021 12 0
-- Peter 2022 01 0