Сделал запрос, но уж больно не оптимизирован, может "гуру" поправят.
Запрос:
with Rates as
(
select ArcDate, Rate from
(
select ArcDate, Rate from
(
select to_date('01092012') ArcDate, 13 Rate from dual
union
select to_date('05112012') ArcDate, 15 Rate from dual
union
select to_date('15112012') ArcDate, 17 Rate from dual
union
select to_date('25112012') ArcDate, 18 Rate from dual
)
)
)
-- Подсчитываю пеню
select
FromDate, TillDate, SummaOut, SummaIn, Rest, Baza, Rate, Days, round(Baza * Rate / 365 * Days)/100 Penalty
from
-- Узнаем базу, убираем не нужные строки
(
select
FromDate, TillDate, SummaOut, SummaIn, Rest,
case when SummaOut>0
then SummaOut
else LAG(Rest) OVER (ORDER BY TillDate)
end Baza,
Rate, Days
from
-- Определяем Курс, Кол-во дней
(
select
FromDate, TillDate, SummaOut, SummaIn,
SUM(SummaOut-SummaIn) OVER(ORDER BY FromDate) as Rest,
(select Rate from Rates where ArcDate=(select MAX(ArcDate) from Rates where ArcDate<=B.TillDate)) Rate,
(TillDate-FromDate)+1 Days
from
-- Определяем Дату C
(
select
case when SummaOut>0
then
TillDate
else
(LAG(TillDate) OVER (ORDER BY TillDate))+1
end FromDate, TillDate, SummaOut, SummaIn
from
-- Определяем Дату По
(
select
case when SummaIn>0 then ArcDate else ArcDate-1 end TillDate, SummaOut, SummaIn
from
-- Разделяем на "Одолжить" и "Возврат", добавляем таблицу с Курсами, сортируем
(
select
Arcdate, sum(decode(OperationId, 1, Summa, 0)) SummaOut, sum(decode(OperationId, 2, Summa, 0)) SummaIn, Null Rate
from
(
select ArcDate, OperationId, Summa from
(
select to_date('01112012') ArcDate, 1 OperationId, 500 Summa from dual
union
select to_date('01112012') ArcDate, 2 OperationId, 80 Summa from dual
union
select to_date('07112012') ArcDate, 2 OperationId, 150 Summa from dual
union
select to_date('09112012') ArcDate, 2 OperationId, 50 Summa from dual
union
select to_date('20112012') ArcDate, 2 OperationId, 220 Summa from dual
)
) Motion
group by ArcDate
union
select ArcDate, 0, 0, Rate from Rates
order by 1
)
-- Конец Разделяем на "Одолжить" и "Возврат", добавляем таблицу с Курсами, сортируем
)
-- Конец Определяем Дату По
) B
-- Конец Определяем Дату C
)
-- Конец Определяем Курс, Кол-во дней
where not (SummaOut=0 and SummaIn=0 and Rest=0)
)
-- Конец Узнаем базу, убираем не нужные строки
-- Конец Подсчитываю пеню