I would like to solve below scenarios for ID 100 & 101 as given output using oracle Analytical functions. any ideas?
TABLE A:
ID ValidFrom ValidTo
100 1/1/2009 12/31/2010
100 1/1/2011 3/31/2012
101 8/1/2013 7/31/2014
101 8/1/2014 8/31/2014
TABLE B
ID ValidFrom ValidTo
100 11/1/2008 12/31/2011
100 2/1/2012 2/29/2012
101 8/1/2013 6/30/2014
101 7/1/2014 8/31/2014
OUTPUT:
ID ValidFrom ValidTo
100 11/1/2008 12/31/2008
100 1/1/2009 12/31/2010
100 1/1/2011 12/31/2011
100 1/1/2012 1/31/2012
100 2/1/2012 2/29/2012
100 3/1/2012 3/31/2012
---------------------------
101 8/1/2013 6/30/2014
101 7/1/2014 7/31/2014
101 8/1/2014 8/31/2014
This query, using analytic lead() does the job. Column note shows if row comes from your data or if it is missing gap:
select id, d1, d2, case dir when 3 then 'GAP' end note
from (
select id,
case when dir = 2
and lead(dir) over (partition by id order by dt) = 1
and lead(dt) over (partition by id order by dt) <> dt + 1
then dt + 1
else dt
end d1,
case when dir = 2
and lead(dir) over (partition by id order by dt) = 1
and lead(dt) over (partition by id order by dt) <> dt + 1
then 3
else dir
end dir,
case when lead(dir) over (partition by id order by dt) = 1
then lead(dt) over (partition by id order by dt) - 1
else lead(dt) over (partition by id order by dt)
end d2
from (
select * from a unpivot (dt for dir in (validfrom as 1, validto as 2)) union
select * from b unpivot (dt for dir in (validfrom as 1, validto as 2)) ) )
where dir in (1, 3)
At first data is unpivoted just to have all dates in one column, it's easier for further analytics. Union removes duplicated values. Column dir informs if this is from or to date. Then lead logic is applied, depending on type of this direction. I think it can be simplified somewhat :)