Having trouble in a PL/SQL program

Question

I have a table(pay_period) as following

pay_period

period_id   list_id    start_date    end_date    price
1           100        2017-01-01    2017-08-31  100
2           100        2017-09-01    2017-12-31  110
3           101        2017-01-01    2017-08-31  75

Now I have list_id, checkin_date, checkout_date

list_id          100
checkin_date     2017-08-25
checkout_date    2017-09-10

I need to calculate the price of a list for the period from checkin date to checkout date. therefore the calculation is supposed to be

7 * 100 + 10 * 110

I am thinking to do it with a for loop, if there is any other better way to do it, can you please suggest?

score 0 · Answer 1 · answered Oct 30 '17 at 14:54

You have to see if the checkin_date and checkout_date are into the same period_id. 1.1 If yes, you multiply the price with the nunmber of days. 1.2 If no, you have count the days between checkin_day untill the end of your period 1 and multiply with the corresponding price, then do the same with checkout_day and beginning of next period.

Note: i guess it might happen to have more than 2 prices per list_id. for example:

period_id   list_id    start_date    end_date    price
1           100        2017-01-01    2017-04-30  100
2           100        2017-05-01    2017-09-30  110
3           100        2017-10-01    2017-12-31  120
4           101        2017-01-01    2017-08-31  75

and the calculation period to be:

list_id          100
checkin_date     2017-03-01
checkout_date    2017-11-10

In this case, yes, the solution would be to have a CURSOR where to keep the prices for list_id and periods; loop through it and compare the checkin_date and checkout_date with each record.

Best, Mikcutu.

score 0 · Answer 2 · answered Nov 01 '17 at 11:20

You can do the following for a much cleaner code. Although it is purely sql, I am using a function to make it code better to understand.

Create a generic function which gets you the number of overlapping days in 2 different date range.

CREATE OR REPLACE FUNCTION fn_count_range 
( p_start_date1 IN DATE, 
p_end_date1   IN DATE, 
p_start_date2 IN DATE, 
p_end_date2 IN DATE ) RETURN NUMBER AS 

v_days NUMBER;

BEGIN 
IF p_end_date1 < p_start_date1 OR p_end_date2 < p_start_date2 THEN 
 RETURN 0;
END IF;

SELECT COUNT(*) INTO v_days
FROM (
        (SELECT p_start_date1 + LEVEL - 1
         FROM dual CONNECT BY LEVEL <= p_end_date1 - p_start_date1 + 1 ) INTERSECT
        (SELECT p_start_date2 + LEVEL - 1
         FROM dual CONNECT BY LEVEL <= p_end_date2 - p_start_date2 + 1 ) );

RETURN v_days;

END;

/

Now, your query to calculate the total price is simplified.

WITH lists ( list_id,
             checkin_date,
             checkout_date) AS
  ( SELECT 100,
           TO_DATE('2017-08-25','YYYY-MM-DD'),
           TO_DATE('2017-09-10','YYYY-MM-DD')
   FROM dual) --Not required if you have a lists table.
SELECT l.list_id,
       SUM(fn_count_range(start_date,end_date,checkin_date,checkout_date) * price) total_price
FROM pay_period p
JOIN lists l ON p.list_id = l.list_id
GROUP BY l.list_id;

Having trouble in a PL/SQL program

2 Answers2