sql - 如何从日期计算费率
问题描述
我正在创建函数,该函数将根据速率和日期之间的天数计算金额。
通过循环,我需要检查哪个速率适合 v_date。
CREATE TABLE INTEREST_RATE_TAB
(
ID VARCHAR2(4 BYTE) NOT NULL,
NAME VARCHAR2(100 BYTE) NOT NULL,
RATE NUMBER NOT NULL,
START_DATE DATE NOT NULL,
END_DATE DATE
)
Insert into INTEREST_RATE_TAB
(ID, NAME, RATE, START_DATE, END_DATE)
Values
('1', 'RATE ', 1.2, TO_DATE('01/01/1999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/23/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into INTEREST_RATE_TAB
(ID, NAME, RATE, START_DATE, END_DATE)
Values
('2', 'RATE II', 0.2, TO_DATE('12/24/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS');
Insert into INTEREST_RATE_TAB
(ID, NAME, RATE, START_DATE, END_DATE )
Values
('3', 'RATE III', 1.2, TO_DATE('11/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
例如 :
如果它只涵盖一个时期,例如如果 v_date 是 01.01.2000 而 v_date_payout 是 03.01.2000。然后在循环中将有 v_amount = 1.2 * 60 (03.01.2000 - 01.01.2000)。
但如果它涵盖两个或多个时期,例如如果 v_date 是 12.01.2016 并且 v_date_payout 是 01.01.2018,那么循环将有 v_amount = 1,2 * 23 (12.23.2016-12.01.2016) 然后 v_amount = 0.2 * 332 (11/21/2017-12/24/2016) 然后 v_amount = 1,2 * 39 (01.01.2018 - 11/22/2017)
解决方案
我想我知道你在这里需要什么。不需要循环,因为单个 SQL 就可以解决问题。提示停止考虑程序步骤(即循环)。而是考虑集体处理的数据集。
首先要意识到您有 4 个条件可以产生全部或部分预期结果。如下图所示:
LET
Rs ==> rate.start_date
Re ==> rate.end_date
Vd ==> v_date
Vp ==> v_date_payout
Case 1: Vd>=Rs & Vp<=Re Days = Vp-Vd
Rs--------------------------Re
Vd---------------Vp
Case 2: Vd<=Rs & (Vp>=Rs & Vp<=Re) Days = Vp-Rs
Rs--------------------------Re
Vd---------------Vp
Case 3: Vd>=Rs # Vp>=Re Days = Re-Vd
Rs--------------------------Re
Vd---------------Vp
Case 4: Vd<Rs & Vp>=Re Days Re-Rs
Rs--------------------------Re
Vd-------------------------------------Vp
Other Cases: Exhibits 2 cases where Vd---Vp lies totally outside Rs----Re.
These will generate NO amount and so not selected for calculation.
Rs--------------Re
Vd----Vp Vd----Vp
该解决方案包括两个步骤:首先,确定范围 Vd----Ve 和范围 Rs-----Re 之间的重叠。在此步骤中,还提取应用该费率的费率和天数。其次为每个贡献范围计算每个范围的部分贡献(可以从最终丢弃)和范围 Vp----Ve 的总和。
with irt as
-- resolve null in end_date default to sysdate
(select id, name, rate,start_date Rs, nvl(end_date,trunc(sysdate)) Re from interest_rate_tab)
-- generate example v_date, v_date_payout sets
, vdata as
( select 'v1' vid,date '2000-01-01' Vd, date '2000-03-01' Vp from dual union all
select 'v2', date '2016-12-01', date '2018-01-01' Vp from dual
)
select t.*
, days*rate range_amount --contribution of each range
, sum(days*rate) over (partition by vid) total_amount -- for vid total
from ( --Determinr Range, extract rate and calculate days to apply rate
-- Vid, rate, days all that's needed, others included for verification purposes, but can e eliminted from final
select vid,Vd,Vp,Rs,Re,Rate
, case when ( Vd >= Rs and Vp < Re ) then Vp-Vd
when ( Vd<=Rs and (Vp>=Rs and Vp<=Re)) then Vp-Rs
when ( Vd>=Rs And Vp>=Re) then Re-Vd
when ( Vd<Rs and Vp>=Re) then Re-Rs
else 0
end days
-- following case staement for display of how daays were derived, for testing, but can e eliminted from fina
, case when ( Vd >= Rs and Vp < Re )
then '( Vd >= Rs and Vp < Re ) ==>Vp-Vd'
when ( Vd<=Rs and (Vp>=Rs and Vp<=Re))
then '( Vd<=Rs and (Vp>=Rs and Vp<=Re)) ==>Vp-Rs'
when ( Vd>=Rs And Vp>=Re)
then '( Vd>=Rs And Vp>=Re) ==>Re-Vd'
when ( Vd<Rs and Vp>=Re)
then '( Vd<Rs and Vp>=Re) ==>Re-Rs'
else 'Error'
end dd
from irt
join vdata
on ( ( Vd >= Rs and Vp < Re )
or ( Vd<=Rs and (Vp>=Rs and Vp<=Re))
or ( Vd>=Rs And Vp>=Re)
or ( Vd<Rs and Vp>=Re)
)
) t
order by vid;
-- resolve null in end_date default to sysdate
(select id, name, rate,start_date Rs, nvl(end_date,trunc(sysdate)) Re from interest_rate_tab)
-- generate example v_date, v_date_payout sets
, vdata as
( select 'v1' vid,date '2000-01-01' Vd, date '2000-03-01' Vp from dual union all
select 'v2', date '2016-12-01', date '2018-01-01' Vp from dual
)
select t.*
, days*rate amount_part --individual section amount
, sum(days*rate) over (partition by vid) --total amount for each vid
from (
with irt as
-- resolve null in end_date default to sysdate
(select id, name, rate,start_date Rs, nvl(end_date,trunc(sysdate)) Re from interest_rate_tab)
-- generate example v_date, v_date_payout sets
, vdata as
( select 'v1' vid,date '2000-01-01' Vd, date '2000-03-01' Vp from dual union all
select 'v2', date '2016-12-01', date '2018-01-01' Vp from dual
)
select vid,Vd,Vp,Rs,Re,Rate
-- determine range overlap -- calculate days
, case when ( Vd >= Rs and Vp < Re ) then Vp-Vd
when ( Vd<=Rs and (Vp>=Rs and Vp<=Re)) then Vp-Rs
when ( Vd>=Rs and Vp>=Re) then Re-Vd
when ( Vd<Rs and Vp>=Re) then Re-Rs
else 0
end days
-- following case statement for display of how days were derived, for testing, when validated delete
, case when ( Vd >= Rs and Vp < Re )
then '( Vd >= Rs and Vp < Re ) ==>Vp-Vd'
when ( Vd<=Rs and (Vp>=Rs and Vp<=Re))
then '( Vd<=Rs and (Vp>=Rs and Vp<=Re)) ==>Vp-Rs'
when ( Vd>=Rs And Vp>=Re)
then '( Vd>=Rs And Vp>=Re) ==>Re-Vd'
when ( Vd<Rs and Vp>=Re)
then '( Vd<Rs and Vp>=Re) ==>Re-Rs'
else 'Error'
end dd
from irt
join vdata
on ( ( Vd >= Rs and Vp < Re )
or ( Vd<=Rs and (Vp>=Rs and Vp<=Re))
or ( Vd>=Rs And Vp>=Re)
or ( Vd<Rs and Vp>=Re)
)
) t
order by vid;
最后是测试完成后的“清理”版本。
with irt as
-- end_datesolve null in end_date default to sysdate
(select id, name, rate,start_date, nvl(end_date,trunc(sysdate)) end_date from interest_rate_tab)
-- generate example v_date, v_date_payout sets
, v_data as
( select 'v1' vid,date '2000-01-01' v_date, date '2000-03-01' v_date_payment from dual union all
select 'v2', date '2016-12-01', date '2018-01-01' v_date_payment from dual
)
select distinct vid, v_date, v_date_payment
, sum(days*rate) over (partition by vid) total_amount -- for vid total
from ( --Determine Range, extract rate, and calculate days to apply rate
select vid,v_date,v_date_payment,start_date,end_date,Rate
, case when ( v_date >= start_date and v_date_payment < end_date ) then v_date_payment-v_date
when ( v_date<=start_date
and v_date_payment>=start_date
and v_date_payment<=end_date
) then v_date_payment-start_date
when ( v_date>=start_date And v_date_payment>=end_date) then end_date-v_date
when ( v_date<start_date and v_date_payment>=end_date) then end_date-start_date
else 0
end days
from irt
join v_data
on ( ( v_date >= start_date and v_date_payment < end_date )
or ( v_date<=start_date and (v_date_payment>=start_date and v_date_payment<=end_date))
or ( v_date>=start_date And v_date_payment>=end_date)
or ( v_date<start_date and v_date_payment>=end_date)
)
) t
order by vid;
推荐阅读
- javascript - 2005-07-01 1:01:01 是什么日期时间格式/标准?
- python - 使用已安装的驱动器在 colab 中加载 .npz
- mediawiki - 列出所有具有自定义标题而不是规范标题的子页面
- android - BroadcastReciever 不工作或 AlarmManager 未设置
- c++ - std::max 在统一初始化和 constexpr int 下表现出意外
- kotlin - kotlin var 的工作方式就像它是不可变的
- laravel - 变形到许多 Laravel 关系
- bash - 没有左值的回显中的重定向运算符?
- html - 网格单元内弹性项目之间的图像导致水平溢出
- reactjs - 在 React 中使用 Knex 查询现有的 Sqlite3 数据库