sql - 查找日期范围内存在的天数

Question

+-------+-----------+------+----------------------+----------------------+
|RATE_ID|DESCRIPTION|CHARGE|FROM_DATE             |TO_DATE               |
+-------+-----------+------+----------------------+----------------------+
|1      |small      |100   |01/01/2014 12:00:00 AM|31/03/2014 12:00:00 AM|
+-------+-----------+------+----------------------+----------------------+
|2      |mediam     |200   |01/04/2014 12:00:00 AM|04/04/2014 12:00:00 AM|
+-------+-----------+------+----------------------+----------------------+
|3      |big        |300   |05/04/2014 12:00:00 AM|31/12/2014 12:00:00 AM|
+-------+-----------+------+----------------------+----------------------+

让上面的日期范围内的费用示例表，我将有一个输入 start_date = to_date('30/mar/2014','dd/mon/yyyy')和 end_date = to_date('05/apr/2014','dd/mon/yyyy') .

所以输入日期包含在两天的费用 100 (rate_id = 1) 中，
4天充200和1天充300共 1300 .

是否有任何简单的方法可以找到给定范围内存在的天数，以便我可以计算 total_charge。目前我正在使用 PL/SQL 使用 loop去寻找存在。

总的来说:
从输入，

30 and 31st march belongs to small(100 charge) => 100* 2 = 200
1, 2, 3, 4 of april belongs to medium( 200 charge) => 200*4 = 800
5th april belongs to big ( 300 charge) => 300*1  = 300
so
the total:= 200 + 800 + 300 = 1300

提前致谢。

Answer 1

您可以使用 CONNECT BY 生成结束日期和开始日期之间的天数列表，并将此列表与费率列表连接起来:

with v_days as (
  SELECT TRUNC (to_date('2014-04-05', 'YYYY-MM-DD') - ROWNUM + 1) dt
  FROM DUAL 
  CONNECT BY ROWNUM <= (to_date('2014-04-05', 'YYYY-MM-DD') + 1 -
      to_date('2014-03-30', 'YYYY-MM-DD'))  
  ),
v_rates as (
  select 1 rate_id, 'small' rate,  100 charge, 
    to_date('2014-01-01', 'YYYY-MM-DD') start_date, 
    to_date('2014-03-31', 'YYYY-MM-DD') end_date 
  from dual union all
  select 2 rate_id, 'medium' rate, 200 charge, 
    to_date('2014-04-01', 'YYYY-MM-DD') start_date,  
    to_date('2014-04-04', 'YYYY-MM-DD') end_date from dual 
  union all
  select 3 rate_id, 'big' rate,    300 charge, 
    to_date('2014-04-05', 'YYYY-MM-DD') start_date, 
    to_date('2014-12-31', 'YYYY-MM-DD') end_date from dual
)  
select sum(charge) as total_charge from (
  select d.*, r.* from v_days d
  join v_rates r on d.dt >= r.start_date and d.dt <= r.end_date
  order by d.dt
)

解释:

v_days 生成开始日期和结束日期之间的天数列表(每天一行)

v_rates 仅包含您提供的费率

然后我们加入这两个子查询 - 如果给定日期在汇率的开始和结束日期之间，则它属于汇率

最后，我们只需将费用相加即可得到总费用