1 2 Previous Next 16 Replies Latest reply on Feb 18, 2020 3:11 PM by Ranagal Go to original post
      • 15. Re: Extract records which are present for two or more days in a row
        Ranagal

        You can use the below code to achieve as you are not on 12c.

         

        Data setup:

         

        create table inputs as

        with inputs as

        (

        select '9G01038' as hr_coil_id, to_date('31/07/2019 11:56:43', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01038' as hr_coil_id, to_date('31/07/2019 12:01:02', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01038' as hr_coil_id, to_date('31/07/2019 12:04:02', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01038' as hr_coil_id, to_date('01/08/2019 12:01:32', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01037' as hr_coil_id, to_date('31/07/2019 12:02:03', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01037' as hr_coil_id, to_date('31/07/2019 12:16:10', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01037' as hr_coil_id, to_date('31/07/2019 13:15:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01036' as hr_coil_id, to_date('31/07/2019 13:16:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01034' as hr_coil_id, to_date('31/07/2019 14:15:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01034' as hr_coil_id, to_date('01/08/2019 14:25:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01034' as hr_coil_id, to_date('02/08/2019 14:30:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

        select '9G01033' as hr_coil_id, to_date('31/07/2019 13:15:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual

        )

        select * from inputs

         

        Solution:

         

        select  t1.*

        from    inputs t1

        where

        exists

        (

            select  null

            from    inputs t2

            where  t2.hr_coil_id = t1.hr_coil_id and

                    trunc(t2.sample_time) >= trunc(t1.sample_time) + 1 or

                    trunc(t2.sample_time) < trunc(t1.sample_time)

        )

         

        Output:

         

         

        Regards,

        Ranagal

        • 16. Re: Extract records which are present for two or more days in a row
          Ranagal

          Or even this:

           

          with l_val as

          (

              select  t.*, last_value(flag ignore nulls) over (partition by hr_coil_id order by sample_time rows between unbounded preceding and unbounded following) flag2

              from  

              (

                  select  t.*,

                          case when trunc(sample_time) >= trunc((lag(sample_time) over (partition by hr_coil_id order by sample_time))) + 1 then 'Y' end  as flag

                  from    inputs t

              ) t

          )

          select  hr_coil_id, sample_time

          from    l_val

          where flag2 = 'Y'

           

          Regards,

          Ranagal

          1 2 Previous Next