1 2 Previous Next 17 Replies Latest reply on May 6, 2010 7:04 AM by WestDrayton

# algorithm with area filling.

I have table T defined below.
All 4 columns contain only positive integer data, minimal value is 1 in all columns.
1. Column pairs (STARTVAL, ENDVAL) define integer range. And we can be sure that there is at least 2 records with same (STARTVAL, ENDVAL) pair. For example pair "2-5" has 2 records and pair 5-10 has 3 records, there is no pair that has only one record. Data surely have been inserted as STARTVAL < ENDVAL, so that there is not pairs with same start and end value.

2. Column pairs (FILLSTART, FILLEND) are suppoused to fill perfectly range (STARTVAL, ENDVAL) for the one (STARTVAL, ENDVAL) pair. Data surely have been inserted as FILLSTART< FILLEND, so that there is not pairs with same start and end value. For example for (STARTVAL, ENDVAL)=(2-5) we have fillers (FILLSTART, FILLEND)=(2-3) and (FILLSTART, FILLEND)=(4-5), so we can say that logical unit (STARTVAL, ENDVAL)=(2-5) is perfectly filled. "Pefectly filled" means that no additional area is filled and that no are is filled more than once. For example region "3-6" has in fillers not needed addional area filled in point 7-7 and area 4-4 intersects for both records there as you see.
``````with T as
(  --OK. Case 2-5 filled with 2-3 and 4-5.
select 2 StartVal, 5 EndVal, 2 FillStart, 3 FillEnd from dual union all
select 2 StartVal, 5 EndVal, 4 FillStart, 5 FillEnd from dual union all
--NOT OK. Case 3-6 filled with 3-4 and 4-7, point 4-4 is fileld more than once, point 7-7 is not needed.
select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 3 StartVal, 6 EndVal, 4 FillStart, 7 FillEnd from dual union all
--NOT OK. Case 4-7 filled with 3-4 and 4-7, too much filled in point "4-4" and not needed point "3-3"
select 4 StartVal, 7 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 4 StartVal, 7 EndVal, 4 FillStart, 7 FillEnd from dual union all
--NOT OK. Case 4-8 filled with 4-5 and 7-8, missing is point 6-6
select 4 StartVal, 8 EndVal, 4 FillStart, 5 FillEnd from dual union all
select 4 StartVal, 8 EndVal, 7 FillStart, 8 FillEnd from dual union all
--OK. Case 5-10 filled with 5-6 and 7-8 and 9-10
select 5 StartVal, 10 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 7 FillStart, 8 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual union all
--NOT OK. Case 5-11 filled with 5-6 and 8-9 and 10-11, missing is point 7-7.
select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 8 FillStart, 9 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 10 FillStart, 11 FillEnd from dual
)
select * from T
order by 1,2,3,4;
/*
2     5     2     3
2     5     4     5--OK!
3     6     3     4
3     6     4     7--NOT OK: intersects at (4,4), and (7,7) is out of range (3,6)
4     7     3     4
4     7     4     7--NOT OK: intersects at (4,4)
4     8     4     5
4     8     7     8--NOT OK: area (6,6) is not filled
5     10     5     6
5     10     7     8
5     10     9     10--OK!
5     11     5     6
5     11     8     9--NOT OK: area (7,7) not filled
5     11     10     11
*/``````
I want to display all (STARTVAL, ENDVAL) pairs that doesnt have "Perfect filling" in columns (FILLSTART, FILLEND). The query should output pairs that i have marked as "NOT OK"..or it should marked only those which i have marked as "OK"- doesn't matter which output, i will later customize query i think. My point is to determine if the filelrs fill perfectly the region or not, if they don't fill then i raise business error later.
How to write such query?

My initial query looks like this:
``````select * from T T2,
(select T.STARTVAL, T.ENDVAL, min(T.FILLSTART) MINFILLSTART, max(T.FILLEND) MAXFILLEND,
SUM(T.FILLEND - T.FILLSTART) SUMFILLER from T
group by T.STARTVAL, T.ENDVAL) MINMAX
where T2.STARTVAL = MINMAX.STARTVAL and T2.ENDVAL = MINMAX.ENDVAL
and T2.STARTVAL = MINMAX.MINFILLSTART
--The rightmost Filler should end with same value as T2.ENDVAL:
and T2.ENDVAL = MINMAX.MAXFILLEND
order by 1, 2, 3, 4;``````
It shows values MINFILLSTARTand MAXFILLEND which define fillers minimal and maximal value, but this is not useful still. Maybe "SUMFILLER" could be useful?

Edited by: CharlesRoos on May 2, 2010 6:30 AM

Edited by: CharlesRoos on May 2, 2010 6:36 AM

Edited by: CharlesRoos on May 2, 2010 6:39 AM

Edited by: CharlesRoos on May 2, 2010 6:48 AM
• ###### 1. Re: algorithm with area filling.
``````with T as
(  --OK. Case 2-5 filled with 2-3 and 4-5.
select 2 StartVal, 5 EndVal, 2 FillStart, 3 FillEnd from dual union all
select 2 StartVal, 5 EndVal, 4 FillStart, 5 FillEnd from dual union all
--NOT OK. Case 3-6 filled with 3-4 and 4-7, point 4-4 is fileld more than once, point 7-7 is not needed.
select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 3 StartVal, 6 EndVal, 4 FillStart, 7 FillEnd from dual union all
--NOT OK. Case 4-7 filled with 3-4 and 4-7, too much filled in point "4-4" and not needed point "3-3"
select 4 StartVal, 7 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 4 StartVal, 7 EndVal, 4 FillStart, 7 FillEnd from dual union all
--NOT OK. Case 4-8 filled with 4-5 and 7-8, missing is point 6-6
select 4 StartVal, 8 EndVal, 4 FillStart, 5 FillEnd from dual union all
select 4 StartVal, 8 EndVal, 7 FillStart, 8 FillEnd from dual union all
--OK. Case 5-10 filled with 5-6 and 7-8 and 9-10
select 5 StartVal, 10 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 7 FillStart, 8 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual union all
--NOT OK. Case 5-11 filled with 5-6 and 8-9 and 10-11, missing is point 7-7.
select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 8 FillStart, 9 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 10 FillStart, 11 FillEnd from dual
)
select  distinct startval,
endval
from  (
select  startval,
endval,
case
when lag(fillend,1,startval - 1) over(partition by startval,endval order by fillstart) < fillstart - 1 then 'Gap'
when lag(fillend,1,startval - 1) over(partition by startval,endval order by fillstart) >= fillstart then 'Overlap'
when row_number() over(partition by startval,endval order by fillstart desc) = 1 and endval > fillend then 'Gap'
when row_number() over(partition by startval,endval order by fillstart desc) = 1 and endval < fillend then 'Overlap'
else 'OK'
end status
from  t
)
where status != 'OK'
/

STARTVAL     ENDVAL
---------- ----------
3          6
4          7
4          8
5         11

SQL> ``````
SY.
• ###### 2. Re: algorithm with area filling.
Seems correct. Thx.
And seems i understand all the logic behind.
``````with T as
(  --Gap1
select 3 StartVal, 11 EndVal, 3 FillStart, 6 FillEnd from dual union all
select 3 StartVal, 11 EndVal, 8 FillStart, 11 FillEnd from dual union all
--Overlap2
select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 7 FillStart, 8 FillEnd from dual union all
--Overlap1
select 5 StartVal, 10 EndVal, 4 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 7 FillStart, 10 FillEnd from dual union all
--Gap2
select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 3 StartVal, 6 EndVal, 5 FillStart, 7 FillEnd from dual
)
select  startval,
endval,
FillStart,
FillEnd,
case
when lag(fillend,1,startval - 1) over(partition by startval,endval order by fillstart) < fillstart - 1 then 'Gap1'
when lag(fillend,1,startval - 1) over(partition by startval,endval order by fillstart) >= fillstart then 'Overlap1'
when row_number() over(partition by startval,endval order by fillstart desc) = 1 and endval > fillend then 'Gap2'
when row_number() over(partition by startval,endval order by fillstart desc) = 1 and endval < fillend then 'Overlap2'
else 'OK'
end status
from  t
order by 1,2;
/*
3     6     3     4     OK
3     6     5     7     Overlap2
3     11     3     6     OK
3     11     8     11     Gap1
5     10     4     6     Overlap1
5     10     7     10     OK
5     11     5     6     OK
5     11     7     8     Gap2
*/``````
• ###### 3. Re: algorithm with area filling.
I have one more additional requirement: all 4 columns have now additional attribute that describes if the value is in Months or in Years.
For example in Case1 all vlaues are in Monthes, Case1 is valid. Case2 has also all columns in both records as monthes, but is not Valid, because area (47-47) overlaps. Case3 has on first record FillStart as 3 Years (which is 36 monthes) and is Valid. All 4 columns can have in any combination the Month/Year choosen.
``````with T as
(  --Case1 OK.
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case2 NOT OK.
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 35 FillStart, 'Month' TypeFS, 47 FillEnd, 'Month' TypeFE from dual union all
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 47 FillStart, 'Month' TypeFS, 215 FillEnd, 'Month' TypeFE from dual union all
--Case3 OK.
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case4 NOT OK.
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 48 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case5 OK.
select 'Case5' Segment, 3 StartVal, 'Year' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case5' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case6 OK.
select 'Case6' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case6' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 61 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all
--Case7 NOT OK.
select 'Case7' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 35 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case7' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 60 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
(case when TypeSV='Year' then StartVal*12 else StartVal end) m_StartVal,
(case when TypeEV='Year' then EndVal*12 else EndVal end) m_EndVal,
(case when TypeFS='Year' then FillStart*12 else FillStart end) m_FillStart,
(case when TypeFE='Year' then FillEnd*12 else FillEnd end) m_FillEnd,
case
when lag(fillend,1,startval - 1) over(partition by Segment, startval,endval order by fillstart) < fillstart - 1 then 'Gap1'
when lag(fillend,1,startval - 1) over(partition by Segment, startval,endval order by fillstart) >= fillstart then 'Overlap1'
when row_number() over(partition by Segment, startval,endval order by fillstart desc) = 1 and endval > fillend then 'Gap2'
when row_number() over(partition by Segment, startval,endval order by fillstart desc) = 1 and endval < fillend then 'Overlap2'
else 'OK'
end status
from  t
order by 1,2;``````
Edited by: CharlesRoos on May 3, 2010 12:32 AM
• ###### 4. Re: algorithm with area filling.
There is hierarchicalquery version :D
``````with T as
(  --OK. Case 2-5 filled with 2-3 and 4-5.
select 2 StartVal, 5 EndVal, 2 FillStart, 3 FillEnd from dual union all
select 2 StartVal, 5 EndVal, 4 FillStart, 5 FillEnd from dual union all
--NOT OK. Case 3-6 filled with 3-4 and 4-7, point 4-4 is fileld more than once, point 7-7 is not needed.
select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 3 StartVal, 6 EndVal, 4 FillStart, 7 FillEnd from dual union all
--NOT OK. Case 4-7 filled with 3-4 and 4-7, too much filled in point "4-4" and not needed point "3-3"
select 4 StartVal, 7 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 4 StartVal, 7 EndVal, 4 FillStart, 7 FillEnd from dual union all
--NOT OK. Case 4-8 filled with 4-5 and 7-8, missing is point 6-6
select 4 StartVal, 8 EndVal, 4 FillStart, 5 FillEnd from dual union all
select 4 StartVal, 8 EndVal, 7 FillStart, 8 FillEnd from dual union all
--OK. Case 5-10 filled with 5-6 and 7-8 and 9-10
select 5 StartVal, 10 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 7 FillStart, 8 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual union all
--NOT OK. Case 5-11 filled with 5-6 and 8-9 and 10-11, missing is point 7-7.
select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 8 FillStart, 9 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 10 FillStart, 11 FillEnd from dual)
select StartVal,EndVal
from (select StartVal,EndVal,FillStart,FillEnd,
min(FillStart) over(partition by StartVal,EndVal) as MinFillStart,
count(*) over(partition by StartVal,EndVal) as cnt
from t)
where connect_by_IsLeaf = 1
and (EndVal != FillEnd or  Level != cnt)
connect by nocycle prior StartVal = StartVal
and prior EndVal = EndVal
and prior FillEnd+1 = FillStart;

STARTVAL  ENDVAL
--------  ------
3       6
4       7
4       8
5      11``````
• ###### 5. Re: algorithm with area filling.
Thx, but Look my last post.
All 4 columns can be either Month or Year values.
How the query should be then?
• ###### 6. Re: algorithm with area filling.
We can calc using case expression ;-)
``````col path for a30

with T as
(  --Case1 OK.
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case2 NOT OK.
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 35 FillStart, 'Month' TypeFS, 47 FillEnd, 'Month' TypeFE from dual union all
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 47 FillStart, 'Month' TypeFS, 215 FillEnd, 'Month' TypeFE from dual union all
--Case3 OK.
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case4 NOT OK.
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 48 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case5 OK.
select 'Case5' Segment, 3 StartVal, 'Year' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case5' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case6 OK. OK??? I suppose this is not OK
select 'Case6' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case6' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 61 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all
--Case7 NOT OK.
select 'Case7' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 35 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case7' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 60 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual
)
select Segment,StartVal,EndVal,sys_connect_by_path(FillStart || '->' || FillEnd,',') as path
from (select Segment,
case TypeSV when 'Year' then 12*StartVal  else StartVal end as StartVal,
case TypeEV when 'Year' then 12*EndVal    else EndVal end as EndVal,
case TypeFS when 'Year' then 12*FillStart else FillStart end as FillStart,
case TypeFE when 'Year' then 12*FillEnd   else FillEnd end as FillEnd,
min(case TypeFS when 'Year' then 12*FillStart else FillStart end)
over(partition by Segment) as MinFillStart,
count(*) over(partition by Segment) as cnt
from t)
where connect_by_IsLeaf = 1
and (EndVal != FillEnd or  Level != cnt)
connect by nocycle prior Segment = Segment
and prior FillEnd+1 = FillStart;

SEGME   STARTVAL     ENDVAL  PATH
-----  ---------  ---------  -------
Case2         36        216  ,35->47
Case4         36        216  ,36->48
Case6         36        216  ,36->48
Case7         36        216  ,35->48``````
• ###### 7. Re: algorithm with area filling.
Yes, case6 is not ok.
Query seems correct.
Thx.

Edited by: CharlesRoos on May 3, 2010 2:45 AM
• ###### 8. Re: algorithm with area filling.
If there is below data case which min(FillStart) > StartVal,
we have to add Level > 2 or prior FillStart = StartVal at connect by clause :-)
``````with T as
(  select 1 StartVal, 10 EndVal, 3 FillStart,  6 FillEnd from dual union all
select 1 StartVal, 10 EndVal, 7 FillStart,  8 FillEnd from dual union all
select 1 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual)
select StartVal,EndVal
from (select StartVal,EndVal,FillStart,FillEnd,
min(FillStart) over(partition by StartVal,EndVal) as MinFillStart,
count(*) over(partition by StartVal,EndVal) as cnt
from t)
where connect_by_IsLeaf = 1
and (EndVal != FillEnd or Level != cnt)
connect by nocycle (Level > 2 or prior FillStart = StartVal) //to add this logic
and prior StartVal = StartVal
and prior EndVal = EndVal
and prior FillEnd+1 = FillStart;``````
• ###### 9. Re: algorithm with area filling.
The other way using recursive with clause B-)
``````with T as
(  select 2 StartVal, 5 EndVal, 2 FillStart, 3 FillEnd from dual union all
select 2 StartVal, 5 EndVal, 4 FillStart, 5 FillEnd from dual union all
select 3 StartVal, 6 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 3 StartVal, 6 EndVal, 4 FillStart, 7 FillEnd from dual union all
select 4 StartVal, 7 EndVal, 3 FillStart, 4 FillEnd from dual union all
select 4 StartVal, 7 EndVal, 4 FillStart, 7 FillEnd from dual union all
select 4 StartVal, 8 EndVal, 4 FillStart, 5 FillEnd from dual union all
select 4 StartVal, 8 EndVal, 7 FillStart, 8 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 7 FillStart, 8 FillEnd from dual union all
select 5 StartVal, 10 EndVal, 9 FillStart, 10 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 5 FillStart, 6 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 8 FillStart, 9 FillEnd from dual union all
select 5 StartVal, 11 EndVal, 10 FillStart, 11 FillEnd from dual),
rec(StartVal,EndVal,FillStart,FillEnd,Val) as(
select StartVal,EndVal,FillStart,FillEnd,FillStart
from t
union all
select StartVal,EndVal,FillStart,FillEnd,Val+1
from rec
where Val+1 <= FillEnd)
select StartVal,EndVal
from rec
group by StartVal,EndVal
having not(EndVal-StartVal+1
=all(count(*),
count(distinct case when Val between StartVal and EndVal
then Val end)))
order by StartVal,EndVal;

StartVal  EndVal
--------  ------
3       6
4       7
4       8
5      11``````
• ###### 10. Re: algorithm with area filling.
CharlesRoos wrote:
All 4 columns can have in any combination the Month/Year choosen.
``````with T as
(  --Case1 OK.
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case2 NOT OK.
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 35 FillStart, 'Month' TypeFS, 47 FillEnd, 'Month' TypeFE from dual union all
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 47 FillStart, 'Month' TypeFS, 215 FillEnd, 'Month' TypeFE from dual union all
--Case3 OK.
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case4 NOT OK.
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 48 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case5 OK.
select 'Case5' Segment, 3 StartVal, 'Year' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case5' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case6 OK.
select 'Case6' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case6' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 61 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all
--Case7 NOT OK.
select 'Case7' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 35 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case7' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 60 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
m_StartVal,
m_EndVal,
m_FillStart,
m_FillEnd,
case
when lag(m_FillEnd,1,m_StartVal - 1) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) <  m_FillStart - 1 then 'Gap1'
when lag(m_FillEnd,1,m_StartVal - 1) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) >= m_FillStart then 'Overlap1'
when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal > m_FillEnd then 'Gap2'
when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal < m_FillEnd then 'Overlap2'
else 'OK'
end status
from  (
select  Segment,
case when TypeSV='Year' then StartVal*12 else StartVal end m_StartVal,
case when TypeEV='Year' then EndVal*12 else EndVal end m_EndVal,
case when TypeFS='Year' then FillStart*12 else FillStart end m_FillStart,
case when TypeFE='Year' then FillEnd*12 else FillEnd end m_FillEnd
from  t
)
order by 1,
2
/

SEGME M_STARTVAL   M_ENDVAL M_FILLSTART  M_FILLEND STATUS
----- ---------- ---------- ----------- ---------- --------
Case1         36        216          36         48 OK
Case1         36        216          49        216 OK
Case2         36        216          35         47 Overlap1
Case2         36        216          47        215 Overlap1
Case3         36        216          36         48 OK
Case3         36        216          49        216 OK
Case4         36        216          36         48 OK
Case4         36        216          48        216 Overlap1
Case5         36        216          36         48 OK
Case5         36        216          49        216 OK
Case6         36        216          36         48 OK

SEGME M_STARTVAL   M_ENDVAL M_FILLSTART  M_FILLEND STATUS
----- ---------- ---------- ----------- ---------- --------
Case6         36        216          61        216 Gap1
Case7         36        216          35         48 Overlap1
Case7         36        216          60        216 Gap1

14 rows selected.

SQL> ``````
SY.
• ###### 11. Re: algorithm with area filling.
See Case10 below, algorithm is wrong there, it shouldnt output Gap1.
``````with T as
(
select 'Case10' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 5 FillEnd, 'Year' TypeFE from dual union all
select 'Case10' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 6 FillStart, 'Year' TypeFS, 7 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
m_StartVal,
m_EndVal,
m_FillStart,
m_FillEnd,
case
when lag(m_FillEnd,1,m_StartVal - 1) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) <  m_FillStart - 1 then 'Gap1'
when lag(m_FillEnd,1,m_StartVal - 1) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) >= m_FillStart then 'Overlap1'
when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal > m_FillEnd then 'Gap2'
when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal < m_FillEnd then 'Overlap2'
else 'OK'
end status
from  (
select  Segment,
case when TypeSV='Year' then StartVal*12 else StartVal end m_StartVal,
case when TypeEV='Year' then EndVal*12 else EndVal end m_EndVal,
case when TypeFS='Year' then FillStart*12 else FillStart end m_FillStart,
case when TypeFE='Year' then FillEnd*12 else FillEnd end m_FillEnd
from  t
)
order by 1,
2,
3,
4;
/*Case10     36     84     36     60     OK
Case10     36     84     72     84     Gap1*/``````
The rules for (FillStart/TypeFS,FillEnd/TypeFE) pairs are as following:

1. If on Prevoius row TypeFE=Year then on Current row FillStart must be as following:
1.1 if on Current row TypeFS=Year, then on current row FillStart must be greater by 1 than on Previous row FillEnd was.
Example of "OK Case":
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 5/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(6/Year, 7/Year)
Example of "NOT OK Case":
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 5/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(5/Year, 7/Year)
1.2 if on Current row TypeFS=Month, then on current row FillStart must be greater by 1 than on Previous row "FillEnd converted to monthes" was.
Example of "OK Case":
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 5/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(61/Month, 7/Year)
Example of "NOT OK Case":
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 5/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(60/Month, 7/Year)

Example of "OK Case" for filling area 3Y-7Y:
row1=(FillStart/TypeFS,FillEnd/TypeFE) =(36/Month, 4/Year)
row2=(FillStart/TypeFS,FillEnd/TypeFE) =(5/Year, 71/Month)
row3=(FillStart/TypeFS,FillEnd/TypeFE) =(6/Year, 7/Year)
• ###### 13. Re: algorithm with area filling.
CharlesRoos wrote:
I see. Here is a fix:
``````with T as
(  --Case1 OK.
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case1' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case2 NOT OK.
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 35 FillStart, 'Month' TypeFS, 47 FillEnd, 'Month' TypeFE from dual union all
select 'Case2' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 47 FillStart, 'Month' TypeFS, 215 FillEnd, 'Month' TypeFE from dual union all
--Case3 OK.
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case3' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case4 NOT OK.
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 3 FillStart, 'Year' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case4' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 48 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case5 OK.
select 'Case5' Segment, 3 StartVal, 'Year' TypeSV, 216 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 48 FillEnd, 'Month' TypeFE from dual union all
select 'Case5' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 49 FillStart, 'Month' TypeFS, 216 FillEnd, 'Month' TypeFE from dual union all
--Case6 OK.
select 'Case6' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case6' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 61 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all
--Case7 NOT OK.
select 'Case7' Segment, 3 StartVal, 'Year' TypeSV, 18 EndVal, 'Year' TypeEV, 35 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case7' Segment, 36 StartVal, 'Month' TypeSV, 216 EndVal, 'Month' TypeEV, 60 FillStart, 'Month' TypeFS, 18 FillEnd, 'Year' TypeFE from dual union all
select 'Case10' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 5 FillEnd, 'Year' TypeFE from dual union all
select 'Case10' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 6 FillStart, 'Year' TypeFS, 7 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
m_StartVal,
m_EndVal,
m_FillStart,
m_FillEnd,
case
when lag(m_FillEnd,1,m_StartVal - gap_val) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) <  m_FillStart - gap_val then 'Gap1'
when lag(m_FillEnd,1,m_StartVal - gap_val) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) >= m_FillStart then 'Overlap1'
when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal > m_FillEnd then 'Gap2'
when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal < m_FillEnd then 'Overlap2'
else 'OK'
end status
from  (
select  Segment,
m_StartVal,
m_EndVal,
m_FillStart,
m_FillEnd,
case
when lag(TypeFE,1,TypeFS) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart)='Year' then 12 else 1 end gap_val
from  (
select  Segment,
case when TypeSV='Year' then StartVal*12 else StartVal end m_StartVal,
case when TypeEV='Year' then EndVal*12 else EndVal end m_EndVal,
case when TypeFS='Year' then FillStart*12 else FillStart end m_FillStart,
case when TypeFE='Year' then FillEnd*12 else FillEnd end m_FillEnd,
TypeFS,
TypeFE
from  t
)
)
order by 1,
2
/

SEGMEN M_STARTVAL   M_ENDVAL M_FILLSTART  M_FILLEND STATUS
------ ---------- ---------- ----------- ---------- --------
Case1          36        216          36         48 OK
Case1          36        216          49        216 OK
Case10         36         84          36         60 OK
Case10         36         84          72         84 OK
Case2          36        216          35         47 Overlap1
Case2          36        216          47        215 Overlap1
Case3          36        216          36         48 OK
Case3          36        216          49        216 OK
Case4          36        216          36         48 OK
Case4          36        216          48        216 Overlap1
Case5          36        216          36         48 OK

SEGMEN M_STARTVAL   M_ENDVAL M_FILLSTART  M_FILLEND STATUS
------ ---------- ---------- ----------- ---------- --------
Case5          36        216          49        216 OK
Case6          36        216          36         48 OK
Case6          36        216          61        216 Gap1
Case7          36        216          35         48 Overlap1
Case7          36        216          60        216 OK

16 rows selected.

SQL>    ``````
SY.

Edited by: Solomon Yakobson on May 4, 2010 1:34 PM
• ###### 14. Re: algorithm with area filling.
Thx, seems perfect.

``````with T as
(  --Case1 OK.
select 'Case11' Segment, 36 StartVal, 'Month' TypeSV, 84 EndVal, 'Month' TypeEV, 36 FillStart, 'Month' TypeFS, 4 FillEnd, 'Year' TypeFE from dual union all
select 'Case11' Segment, 3 StartVal, 'Year' TypeSV, 84 EndVal, 'Month' TypeEV, 5 FillStart, 'Year' TypeFS, 71 FillEnd, 'Month' TypeFE from dual union all
select 'Case11' Segment, 36 StartVal, 'Month' TypeSV, 7 EndVal, 'Year' TypeEV, 6 FillStart, 'Year' TypeFS, 7 FillEnd, 'Year' TypeFE from dual
)
select  Segment,
m_StartVal,
m_EndVal,
m_FillStart,
m_FillEnd,
case
when lag(m_FillEnd,1,m_StartVal - gap_val) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) <  m_FillStart - gap_val then 'Gap1'
when lag(m_FillEnd,1,m_StartVal - gap_val) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart) >= m_FillStart then 'Overlap1'
when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal > m_FillEnd then 'Gap2'
when row_number() over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart desc) = 1 and m_EndVal < m_FillEnd then 'Overlap2'
else 'OK'
end status
from  (
select  Segment,
m_StartVal,
m_EndVal,
m_FillStart,
m_FillEnd,
case
when lag(TypeFE,1,TypeFS) over(partition by Segment,m_StartVal,m_EndVal order by m_FillStart)='Year' then 12 else 1 end gap_val
from  (
select  Segment,
case when TypeSV='Year' then StartVal*12 else StartVal end m_StartVal,
case when TypeEV='Year' then EndVal*12 else EndVal end m_EndVal,
case when TypeFS='Year' then FillStart*12 else FillStart end m_FillStart,
case when TypeFE='Year' then FillEnd*12 else FillEnd end m_FillEnd,
TypeFS,
TypeFE
from  t
)
)
order by 1,
2
;
/*Case11     36     84     36     48     OK
Case11     36     84     60     71     OK
Case11     36     84     72     84     OK*/``````
1 2 Previous Next