You Asked
Hello,
I have the following table:
create table temp (
date_from date,
date_to date,
rate number);
with this data in it:
insert into temp values (date '1973-07-01', date '1992-12-31', 6.75);
insert into temp values (date '1993-01-01', date '2003-12-31', 8.25);
insert into temp values (date '2004-01-01', date '2004-06-30', 9.25);
insert into temp values (date '2004-07-01', date '2005-06-30', 9.75);
insert into temp values (date '2005-07-01', date '2008-06-30', 10.25);
insert into temp values (date '2008-07-01', date '2009-06-30', 10.9);
insert into temp values (date '2009-07-01', date '2010-06-30', 11.3);
insert into temp values (date '2010-07-01', date '2011-06-30', 11.6);
insert into temp values (date '2011-07-01', date '2012-06-30', 11);
insert into temp values (date '2012-07-01', date '2014-06-30', 10);
insert into temp values (date '2014-07-01', null, 10.1);
commit;
I am given a date parameter, p_date, and I want to break down the time spell this date falls in, in order to produce two new spells with the same rate. For instance, if p_date = date '2005-12-31', I do expect to replace the interval [date '2005-07-01', date '2008-06-30'] with the following two intervals
[date '2005-07-01', date '2005-12-31'] and [date '2006-01-01', date '2008-06-30'], both having a rate of 10.25.
Could you help me design a query like:
with q as (
/*REPLACE*/
)
select * from q
order by date_from;
such that, when I provide a replacement for /*REPLACE*/ containing p_date = date '2005-12-31', the above query returns the following result set:
DATE_FROM DATE_TO RATE
01/07/1973 31/12/1992 6.75
01/01/1993 31/12/2003 8.25
01/01/2004 30/06/2004 9.25
01/07/2004 30/06/2005 9.75
01/07/2005 31/12/2005 10.25
01/01/2006 30/06/2008 10.25
01/07/2008 30/06/2009 10.9
01/07/2009 30/06/2010 11.3
01/07/2010 30/06/2011 11.6
01/07/2011 30/06/2012 11
01/07/2012 30/06/2014 10
01/07/2014 10.1
Of course, the query is supposed to work also for dates after date '2014-07-01'.
Thanks in advance.
Livio
and Chris said...
Thanks for providing a complete script and examples.
It's not clear what should happen when you provide a date that is equal to one of the existing start or end dates. For the purposes of this answer I've assumed that the new range starts and ends on the same day. I've also guessed that by "works for dates after 2014-07-01" you mean it splits the last row at the provided date. The last row remains with a null end date.
If these assumptions are incorrect you'll need to update the query based on what these should be.
To split the range, we must first find the row where the provided date >= date_from and < date_to + 1 (or it's null):
select *
from temp
where to_date(:dt, 'dd/mm/yyyy') >= date_from
and (to_date(:dt, 'dd/mm/yyyy') < date_to + 1 or date_to is null)
Combine this with a two row table to double this up. Select the date_from, date_to or supplied date as appropriate:
select case
when r.r = 1 then date_from
else least(to_date(:dt, 'dd/mm/yyyy') + 1, nvl(date_to, to_date(:dt, 'dd/mm/yyyy') + 1))
end date_from,
case
when r.r = 1 then to_date(:dt, 'dd/mm/yyyy')
else date_to
end date_to,
rate
from temp, (select rownum r from dual connect by level <= 2) r
where to_date(:dt, 'dd/mm/yyyy') >= date_from
and (to_date(:dt, 'dd/mm/yyyy') < date_to + 1 or date_to is null)To get the rest of the rows, union all this with those where the date is not in the range:
select date_from, date_to, rate
from temp
where not (to_date(:dt, 'dd/mm/yyyy') >= date_from and
(to_date(:dt, 'dd/mm/yyyy') < date_to + 1 or date_to is null)Putting this all together gives:
var dt varchar2(10);
exec :dt := '31/12/2005';
with q as (
select case
when r.r = 1 then date_from
else least(to_date(:dt, 'dd/mm/yyyy') + 1, nvl(date_to, to_date(:dt, 'dd/mm/yyyy') + 1))
end date_from,
case
when r.r = 1 then to_date(:dt, 'dd/mm/yyyy')
else date_to
end date_to,
rate
from temp, (select rownum r from dual connect by level <= 2) r
where to_date(:dt, 'dd/mm/yyyy') >= date_from
and (to_date(:dt, 'dd/mm/yyyy') < date_to + 1 or date_to is null)
union all
select date_from, date_to, rate
from temp
where not (to_date(:dt, 'dd/mm/yyyy') >= date_from and
to_date(:dt, 'dd/mm/yyyy') < date_to + 1 or date_to is null)
)
select * from q
order by date_from, date_to;
DATE_FROM DATE_TO RATE
------------------- ------------------- ----------
01/07/1973 00:00:00 31/12/1992 00:00:00 6.75
01/01/1993 00:00:00 31/12/2003 00:00:00 8.25
01/01/2004 00:00:00 30/06/2004 00:00:00 9.25
01/07/2004 00:00:00 30/06/2005 00:00:00 9.75
01/07/2005 00:00:00 31/12/2005 00:00:00 10.25
01/01/2006 00:00:00 30/06/2008 00:00:00 10.25
01/07/2008 00:00:00 30/06/2009 00:00:00 10.9
01/07/2009 00:00:00 30/06/2010 00:00:00 11.3
01/07/2010 00:00:00 30/06/2011 00:00:00 11.6
01/07/2011 00:00:00 30/06/2012 00:00:00 11
01/07/2012 00:00:00 30/06/2014 00:00:00 10
01/07/2014 00:00:00 10.1
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment