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

    algorithm with area filling.

    650063
      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
         --The leftmost Filler should start with same value as T2.STARTVAL:
         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.
          Solomon Yakobson
          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.
          1 person found this helpful
          • 2. Re: algorithm with area filling.
            650063
            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.
              650063
              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.
                Aketi Jyuuzou
                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)
                start with FillStart = MinFillStart
                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
                1 person found this helpful
                • 5. Re: algorithm with area filling.
                  650063
                  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.
                    Aketi Jyuuzou
                    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)
                    start with FillStart = MinFillStart
                    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.
                      650063
                      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.
                        Aketi Jyuuzou
                        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)
                        start with FillStart = MinFillStart
                        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;
                        1 person found this helpful
                        • 9. Re: algorithm with area filling.
                          Aketi Jyuuzou
                          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
                          1 person found this helpful
                          • 10. Re: algorithm with area filling.
                            Solomon Yakobson
                            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.
                            1 person found this helpful
                            • 11. Re: algorithm with area filling.
                              650063
                              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)
                              • 12. Re: algorithm with area filling.
                                650063
                                Not answered.
                                • 13. Re: algorithm with area filling.
                                  Solomon Yakobson
                                  CharlesRoos wrote:
                                  Not answered.
                                  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.
                                    650063
                                    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