ORACLE - Expand Date range based on input date range provided

Question

I have multiple date ranges in a table(i.e. TAB1) like below.

START_DATE  |  END_DATE
-------------------------
1-Jan-2004  |  31-Dec-2005
1-Jan-2001  |  31-Dec-2001
1-Jan-2011  |  31-Dec-2015

Now based on an input date range assuming it overlaps at least 1 row in the table, I want to change dates of existing rows.

Example 1 : If the input date range is 1-Jan-2012 | 31-Dec-2017 then Row-3 should be 1-Jan-2011 | 31-Dec-2017. Rest of the rows should be unchanged.

Example 2 : If the input date range is 1-Jan-2007 | 31-Dec-2012 then Row-3 should be 1-Jan-2007 | 31-Dec-2015. Rest of the rows should be unchanged.

Example 3 : If the input date range is 1-Jan-2009 | 31-Dec-2017 then Row-3 should be 1-Jan-2009 | 31-Dec-2017. Rest of the row should be unchanged.

Example 4 : If the input date range is 1-Jan-2005 | 31-Dec-2012 then Row-3 should be 1-Jan-2006 | 31-Dec-2015. Rest of the row should be unchanged.

Example 5 : If the input date range is 1-Jan-2003 | 31-Dec-2003 then Row-1 should be 1-Jan-2003 | 31-Dec-2005. Rest of the row should be unchanged.

Any suggestion of SQL query for above?

Answer 1

It's not beautiful, but may be useful

---

with tbl1 as
(SELECT '1-Jan-2004 | 31-Dec-2005' int FROM dual
UNION ALL
SELECT '1-Jan-2001 | 31-Dec-2001' FROM dual
UNION ALL
SELECT '1-Jan-2011 | 31-Dec-2015' FROM dual),
tbl2 as
(SELECT '1-Jan-2004 | 31-Dec-2005' int FROM dual
UNION ALL
SELECT '1-Jan-2001 | 31-Dec-2001' FROM dual
UNION ALL
SELECT '1-Jan-2007 | 31-Dec-2015' FROM dual)
SELECT * FROM 
(SELECT TO_DATE(TRIM(TRIM( '|' FROM REGEXP_SUBSTR(int, '.+[0-9] \|'))), 'dd-Mon-yyyy', 'NLS_DATE_LANGUAGE=AMERICAN') str,
TO_DATE(TRIM(TRIM( '|' FROM REGEXP_SUBSTR(int, '\|.+'))), 'dd-Mon-yyyy', 'NLS_DATE_LANGUAGE=AMERICAN') end 
FROM tbl1) a,
(SELECT TO_DATE(TRIM(TRIM( '|' FROM REGEXP_SUBSTR(int, '.+[0-9] \|'))), 'dd-Mon-yyyy', 'NLS_DATE_LANGUAGE=AMERICAN') str,
TO_DATE(TRIM(TRIM( '|' FROM REGEXP_SUBSTR(int, '\|.+'))), 'dd-Mon-yyyy', 'NLS_DATE_LANGUAGE=AMERICAN') end
FROM tbl2) b
where a.str >= b.str 
and a.end <= b.end

OUTPUT:

01.01.2011  31.12.2015  01.01.2007  31.12.2015
01.01.2004  31.12.2005  01.01.2004  31.12.2005
01.01.2001  31.12.2001  01.01.2001  31.12.2001

Answer 2

Here is a select you can use:

-- First your sample-data:
WITH tab1(rn, start_date, end_date) AS 
  (SELECT 1, DATE'2004-01-01', DATE'2005-12-31' FROM dual UNION ALL 
   SELECT 2, DATE'2001-01-01', DATE'2001-12-31' FROM dual UNION ALL 
   SELECT 3, DATE'2011-01-01', DATE'2015-12-31' FROM dual)
-- Then the different inputs:
   --, INP(start_date, end_date) AS (SELECT  DATE'2012-01-01', DATE'2017-12-31' FROM dual)
   --, INP(start_date, end_date) AS (SELECT  DATE'2007-01-01', DATE'2012-12-31' FROM dual)
   --, INP(start_date, end_date) AS (SELECT  DATE'2009-01-01', DATE'2017-12-31' FROM dual)
   --, INP(start_date, end_date) AS (SELECT  DATE'2005-01-01', DATE'2012-12-31' FROM dual)
   , INP(start_date, end_date) AS (SELECT  DATE'2003-01-01', DATE'2003-12-31' FROM dual)
-- Get all fitting rows - this is enough for example 1,2,3,5:
   , fit AS (SELECT tab1.rn
                  , LEAST(inp.start_date, tab1.start_date) start_date
                  , GREATEST(inp.end_date, tab1.end_date) end_date
                  , tab1.end_date prev_end
                  , ROW_number() over(ORDER BY tab1.end_date DESC) rw
               FROM tab1
               JOIN inp
                 ON inp.end_date + 1 >= tab1.start_date
                AND inp.start_date - 1 <= tab1.end_date)
-- If there is a second fitting row adjust start_date:
SELECT fit.rn
     , CASE WHEN prev.prev_end IS NULL THEN fit.start_date
            ELSE GREATEST(prev.prev_end + 1, fit.start_date)
       END start_date
     , fit.end_date
  FROM fit
  LEFT JOIN fit prev 
    ON prev.rw = fit.rw + 1
 WHERE fit.rw = 1

You can then use this statement in UPDATE or MERGE to change the row as required.

Answer 3

You can try simple case when to achieve this:

SQL> WITH tab1(start_date, end_date) AS
  2    (SELECT DATE'2004-01-01', DATE'2005-12-31' FROM dual UNION ALL
  3     SELECT DATE'2001-01-01', DATE'2001-12-31' FROM dual UNION ALL
  4     SELECT DATE'2011-01-01', DATE'2015-12-31' FROM dual)
  5  SELECT -- original query starts from here
  6      CASE
  7          WHEN DATE '&&START_DATE' - 1 BETWEEN T1.START_DATE AND T1.END_DATE
  8               OR DATE '&&END_DATE' + 1 BETWEEN T1.START_DATE AND T1.END_DATE
  9          THEN LEAST(T1.START_DATE, DATE '&&START_DATE')
 10          ELSE T1.START_DATE
 11      END AS START_DATE,
 12      CASE
 13          WHEN DATE '&&START_DATE' - 1 BETWEEN T1.START_DATE AND T1.END_DATE
 14               OR DATE '&&END_DATE' + 1 BETWEEN T1.START_DATE AND T1.END_DATE
 15          THEN GREATEST(T1.END_DATE, DATE '&&END_DATE')
 16          ELSE T1.END_DATE
 17      END AS END_DATE
 18  FROM
 19      TAB1 T1; -- example 2
Enter value for start_date: 2007-01-01
Enter value for end_date: 2012-12-31

START_DATE  END_DATE
----------- -----------
01-jan-2004 31-dec-2005
01-jan-2001 31-dec-2001
01-jan-2007 31-dec-2015

SQL>

Cheers!!

Answer 4

As far as I understad the rules

with 
-- test data
t1 (id, START_DATE, END_DATE) as
( 
SELECT 1,  to_date('1-Jan-2004'), to_date('31-Dec-2005')  FROM DUAL UNION ALL
SELECT 2,  to_date('1-Jan-2001'), to_date('31-Dec-2001')  FROM DUAL UNION ALL
SELECT 3,  to_date('1-Jan-2011'), to_date('31-Dec-2015')  FROM DUAL 
)
-- test inputs
, inp (id, START_DATE, END_DATE) as
( 
SELECT 1,  to_date('1-Jan-2012'), to_date('31-Dec-2017')  FROM DUAL UNION ALL
SELECT 2,  to_date('1-Jan-2007'), to_date('31-Dec-2012')  FROM DUAL UNION ALL
SELECT 3,  to_date('1-Jan-2009'), to_date('31-Dec-2017')  FROM DUAL UNION ALL
SELECT 4,  to_date('1-Jan-2005'), to_date('31-Dec-2012')  FROM DUAL UNION ALL
SELECT 5,  to_date('1-Jan-2003'), to_date('31-Dec-2003')  FROM DUAL 
)
--  find matches and extend the original interval
, extended as (
SELECT inp.id inp_id, t1.id id, least(t1.START_DATE, inp.START_DATE) START_DATE, greatest(t1.END_DATE, inp.END_DATE) END_DATE
, row_number() over (partition by inp.id order by least(t1.START_DATE, inp.START_DATE)) rn
FROM t1
join inp on inp.START_DATE <= t1.END_DATE+1 and t1.START_DATE <= inp.END_DATE +1
)
-- select the only (rn=1) original data row the input is applied to, find and exclude intersections with other original data rows
, truncated as 
(select e.inp_Id, e.id
 , case when t1.id is not null then 
      case when e.START_DATE < t1.START_DATE then e.START_DATE else t1.END_DATE + 1 end
   else e.START_DATE end START_DATE
 , case when t1.id is not null then 
      case when e.START_DATE < t1.START_DATE then t1.START_DATE - 1 else e.END_DATE end
   else e.END_DATE end END_DATE
from extended e 
left join t1 on e.id != t1.id  and e.START_DATE <= t1.END_DATE and t1.START_DATE <= e.END_DATE 
where e.rn=1
)
-- see the results after the input is applied
select t1.id, e2.inp_id, coalesce(e2.START_DATE, t1.START_DATE) START_DATE, coalesce(e2.END_DATE,t1.END_DATE) END_DATE
from t1 
left join truncated e2 on t1.id = e2.id
order by e2.inp_id, t1.id;

Returns

INP_ID  ID  START_DATE  END_DATE
1   3   01-JAN-11   31-DEC-17
2   3   01-JAN-07   31-DEC-15
3   3   01-JAN-09   31-DEC-17
4   1   01-JAN-04   31-DEC-10
5   1   01-JAN-03   31-DEC-05
    2   01-JAN-01   31-DEC-01

So inputs 1,2 and 3 update row-3, inputs 4 and 5 update row-1, row-2 is never updated.