## algorithm with area filling.

May 2 2010 — edited May 6 2010
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
This post has been answered by Aketi Jyuuzou on May 3 2010