Hi,
I have a procedure that takes 2 parameters (start and end date) and using some additional data from a "settings" table and some sales transactions, populates an intermediary table.
The settings table was initially as 1 interval / 1 set of data but now there are more intervals, with different settings.
My problem is that starting with the user dates (start and end date) I need to determine from the settings table, all the intervals contained between them, so then I will call the procedure multiple times with all the intervals found.
To be precise, for the given example, if the user is using 01-Jan-2018 and 31-Jul-2018 I need to call the procedure using the following sets of dates:
01-Jan-2018 - 31-Mar-2018
01-Apr-2018 - 31-May-2018
01-Jun-2018 - 30-Jun-2018
01-Jul-2018 - 31-Jul-2018
I can determine the first and the last interval but I don't know how to deal with the middle part where can be two like in the exemple or more, depending on the input dates.
Thank you for any help!
So you're looking for intervals that:
- Start before and end after your start date
- Start before and end after your end date
- Start on or after your start date and end on or before your end date
And for intervals that start or end outside your range, you want to show your range instead?
If so, just put these tests in your where clause. And return the greatest of the row start + variable start. With the least of the row end + variable end.
Which gives:
CREATE TABLE F500 (
ID NUMBER(11) NOT NULL,
TARIF_1 FLOAT(126) NOT NULL,
DATA_START DATE NOT NULL,
DATA_END DATE NOT NULL
);
Insert into F500 Values (0, 0, TO_DATE('01/04/2016', 'DD/MM/YYYY'), TO_DATE('31/03/2017', 'DD/MM/YYYY'));
Insert into F500 Values (1, 1, TO_DATE('01/04/2017', 'DD/MM/YYYY'), TO_DATE('31/03/2018', 'DD/MM/YYYY'));
Insert into F500 Values (2, 2, TO_DATE('01/04/2018', 'DD/MM/YYYY'), TO_DATE('31/05/2018', 'DD/MM/YYYY'));
Insert into F500 Values (3, 3, TO_DATE('01/06/2018', 'DD/MM/YYYY'), TO_DATE('30/06/2018', 'DD/MM/YYYY'));
Insert into F500 Values (4, 4, TO_DATE('01/07/2018', 'DD/MM/YYYY'), TO_DATE('31/12/2019', 'DD/MM/YYYY'));
Insert into F500 Values (5, 5, TO_DATE('01/01/2020', 'DD/MM/YYYY'), TO_DATE('31/12/2020', 'DD/MM/YYYY'));
Insert into F500 Values (6, 6, TO_DATE('01/04/2016', 'DD/MM/YYYY'), TO_DATE('31/12/2020', 'DD/MM/YYYY'));
var start_date varchar2(20);
var end_date varchar2(20);
exec :start_date := '01-JAN-2018';
exec :end_date := '31-JUL-2018';
select id, tarif_1,
greatest ( data_start, to_date ( :start_date, 'dd-mon-yyyy' ) ) start_date,
least ( data_end, to_date ( :end_date, 'dd-mon-yyyy' ) ) end_date
from f500
where (
data_start < to_date ( :start_date, 'dd-mon-yyyy' ) and
data_end >= to_date ( :start_date, 'dd-mon-yyyy' )
) or (
data_start >= to_date ( :start_date, 'dd-mon-yyyy' ) and
data_end <= to_date ( :end_date, 'dd-mon-yyyy' )
) or (
data_start < to_date ( :end_date, 'dd-mon-yyyy' ) and
data_end >= to_date ( :end_date, 'dd-mon-yyyy' )
) ;
ID TARIF_1 START_DATE END_DATE
1 1 01-JAN-2018 00:00:00 31-MAR-2018 00:00:00
2 2 01-APR-2018 00:00:00 31-MAY-2018 00:00:00
3 3 01-JUN-2018 00:00:00 30-JUN-2018 00:00:00
4 4 01-JUL-2018 00:00:00 31-JUL-2018 00:00:00
6 6 01-JAN-2018 00:00:00 31-JUL-2018 00:00:00 But beware!
Date ranges are tricky things. There are 13 ways you can classify how two date ranges overlap. This works for the data you provided. Plus a couple more I threw in.
Ensure you've covered off
all possibilities. Or you're likely to end up with bugs!
Stew Ashton has several articles discussing this, I recommend you read them:
https://stewashton.wordpress.com/2015/06/08/merging-overlapping-date-ranges/