Find the number of days within a date range

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|
+-------+-----------+------+----------------------+----------------------+

Let the sample fee table in the date range be higher, I will have an input, as
start_date = to_date('30/mar/2014','dd/mon/yyyy')well as
end_date = to_date('05/apr/2014','dd/mon/yyyy').

Thus, the input dates are included for two days of charge 100 (rate_id = 1), 4 days of charge 200 and 1 day of charge 300 and all 1300.

Is there an easy way to find the number of days that exist in a given range so that I can calculate total_charge. I am currently using PL / SQL for the above, using loopto find existence.

In general: from the entrance,

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

Thanks in advance.

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 ,
• - ,
• , ,

Answer 2

DATEDIFF() .

SELECT DATEDIFF(day,'01-01-2014','31-03-2014') AS DiffDate

, .

. http://www.w3schools.com/sql/func_datediff.asp

Answer 3

select sum(case
             when trunc(&start_date) <= trunc("to_date") AND
                  trunc(&end_date) >= trunc(from_date) then
              (least(trunc(&end_date), trunc("to_date")) -
              greatest(trunc(&start_date), trunc(from_date)) + 1) * charge
             else
              0
           end) total_charge
  from your_table;

- , start_date end_date (when part case), , (+1 , , , 1 ), .

, case where:

select sum((least(trunc(&end_date), trunc("to_date")) -
           greatest(trunc(&start_date), trunc(from_date)) + 1)
            * charge) total_charge
  from your_table
 where trunc(&start_date) <= trunc("to_date")
   AND trunc(&end_date) >= trunc(from_date);

"TO_DATE", .

Answer 4

with w as (
  select 100 charge, date '2014-01-01' from_date, date '2014-03-31' to_date from dual union all
  select 200 charge, date '2014-04-01' from_date, date '2014-04-04' to_date from dual union all
  select 300 charge, date '2014-04-05' from_date, date '2014-12-31' to_date from dual
),
x as (
  select 
    date '2014-03-30' start_period,
    date '2014-04-05'   end_period
  from
    dual
)
select
sum(
  w.charge * 
    case when x.start_period <= w.  to_date and
              x.  end_period >= w.from_date
    then
       case when x.end_period > w.to_date
            then w.to_date
            else x.end_period
       end
            -
       case when x.start_period < w.from_date 
            then w.from_date
            else x.start_period
       end
       +1
    else
        0
    end
)   result
from
  w cross join x;

Answer 5

AND TO_DATE (SYSDATE, 'dd / mm / yyyy') BETWEEN TO_DATE (SYSDATE, 'dd / mm / yyyy') AND TO_DATE (END_DATE, 'dd / mm / yyyy) AND TO_DATE (START_DATE,' dd / mm / yyyy ') <= TO_DATE (SYSDATE,' dd / mm / yyyy ');