Historicisation tests: time gaps and overlappings

--
--
@set resultset name OLAPs/GAPs ; -- Client command DbVis setting the name of the result tab
with PM
     as (select 1 as TIME_GRANULARITY_IN_DAYS
               ,1 as BOUNDARY_INCLUSIVE_IS_0
           from dual)
    ,BASE
     as (    select [KEY_COLUMNS]
                   ,[FROM_COLUMN]
                   ,case
                       when [UNTIL_COLUMN] >=   to_date('9999-12-31 23:59:59'
                                                       ,'yyyy-mm-dd hh24:mi:ss')
                                              - PM.TIME_GRANULARITY_IN_DAYS
                       then   to_date('9999-12-31 23:59:59'
                                     ,'yyyy-mm-dd hh24:mi:ss')
                            - PM.TIME_GRANULARITY_IN_DAYS
                       else [UNTIL_COLUMN]
                    end                      as [UNTIL_COLUMN]
                   ,lead([FROM_COLUMN]) over (partition by [KEY_COLUMNS]
                                              order by [FROM_COLUMN] asc
                                                      ,[UNTIL_COLUMN] asc)
                                             as [FROM_COLUMN]_NEXT
                   ,lead(case
                            when [UNTIL_COLUMN] >=   to_date('9999-12-31 23:59:59'
                                                            ,'yyyy-mm-dd hh24:mi:ss')
                                                   - PM.TIME_GRANULARITY_IN_DAYS
                            then   to_date('9999-12-31 23:59:59'
                                          ,'yyyy-mm-dd hh24:mi:ss')
                                 - PM.TIME_GRANULARITY_IN_DAYS
                            else [UNTIL_COLUMN]
                         end) over (partition by [KEY_COLUMNS]
                                    order by [FROM_COLUMN] asc
                                            ,[UNTIL_COLUMN] asc)
                                             as [UNTIL_COLUMN]_NEXT
                   ,PM.TIME_GRANULARITY_IN_DAYS
                   ,PM.BOUNDARY_INCLUSIVE_IS_0
               from [TABLE_NAME] -- default of the columns
         inner join PM
                 on 1 = 1
              where 1 = 1
                and (   (    PM.BOUNDARY_INCLUSIVE_IS_0 = 0
                         and   [UNTIL_COLUMN]
                             - [FROM_COLUMN] >= PM.TIME_GRANULARITY_IN_DAYS)
                     or (        PM.BOUNDARY_INCLUSIVE_IS_0 != 0 -- to handle NULL
                         and [UNTIL_COLUMN] > [FROM_COLUMN] -- for boundary excluding, time granularity does not make sense
                         and 1 = 1))
                and 1 = 1)
    ,GAPS
     as (select 'Gap' as FINDING
               ,[KEY_COLUMNS]
               ,[FROM_COLUMN]
               ,[UNTIL_COLUMN]
               ,[FROM_COLUMN]_NEXT
               ,[UNTIL_COLUMN]_NEXT
           from BASE
          where 1 = 1
            and [FROM_COLUMN]_NEXT is not null
            and (   (    BOUNDARY_INCLUSIVE_IS_0 = 0
                     and   [FROM_COLUMN]_NEXT
                         - [UNTIL_COLUMN] > TIME_GRANULARITY_IN_DAYS)
                 or (    BOUNDARY_INCLUSIVE_IS_0 != 0 -- to handle NULL
                     and [FROM_COLUMN]_NEXT > [UNTIL_COLUMN] -- for boundary excluding, time granularity does not make sense
                     and 1 = 1))
            and 1 = 1)
    ,OLAPS
     as (select 'Overlap' as FINDING
               ,[KEY_COLUMNS]
               ,[FROM_COLUMN]
               ,[UNTIL_COLUMN]
               ,[FROM_COLUMN]_NEXT
               ,[UNTIL_COLUMN]_NEXT
           from BASE
          where 1 = 1
            and [FROM_COLUMN]_NEXT is not null
            and (   (    BOUNDARY_INCLUSIVE_IS_0 = 0
                     and   [UNTIL_COLUMN]
                         + TIME_GRANULARITY_IN_DAYS >= [FROM_COLUMN]_NEXT)
                 or (    BOUNDARY_INCLUSIVE_IS_0 != 0 -- to handle NULL
                     and [UNTIL_COLUMN] > [FROM_COLUMN]_NEXT -- for boundary excluding, time granularity does not make sense
                     and 1 = 1))
            and 1 = 1)
select /*+ parallel(auto) */ *
  from OLAPS
union all
select /*+ parallel(auto) */ *
  from GAPS;