7 Replies Latest reply: Dec 7, 2012 9:13 AM by chris227 RSS

    SQL query to check continious records

    743517
      With T1 As ( SELECT ‘E1’ as E ,1100 as T , 15.0787 as SM1 , 16.1345 as EM1 from dual union all
      SELECT ‘E1’ ,1100 , 16.084 , 18.045 from dual union all
      SELECT ‘E2’ ,1100 , 15.0787 , 16.1345 from dual union all
      SELECT ‘E2’ ,1100 , 15.0327 , 18.045 from dual union all
      SELECT ‘E3’ ,1100 , 15.0197 , 16.1445 from dual union all
      SELECT ‘E3’ ,1100 , 16.1445 , 18.045 from dual union all
      SELECT ‘E4’ ,1100 , 13.0787 , 14.0743 from dual union all
      SELECT ‘E4’ ,1100 , 14.0743 , 34.0843 from dual union all
      SELECT ‘E4’ ,1100 , 34.0843 , 54.0743 from
      SELECT ‘E4’ ,1110 , 13.0787 , 14.0743 from dual union all
      SELECT ‘E4’ ,1110 , 14.0743 , 34.0843 from dual union all
      SELECT ‘E4’ ,1110 , 34.0843 , 54.0743 from union all
      SELECT ‘E1’ ,1100 , 19.084 , 20.045 from )

      Main Output should be come as


      E1      1100     15.0787 18.045
      E2 1100 15.0787 16.1345
      E2 1100 15.0327 18.045
      E3 1100 15.0197 18.045
      E4 1100 13.0787 54.0743
      E4 1110 13.0787 54.0743
      E1 1100 19.084 20.045

      could you help me with an sql to get the first output ?


      Logic to derive the above output:

      For a particular set of E and T as long as the EM1 of previous record <=SM1 of next record and SM1 of previous record < = SM2 of next record then consider it as a continious record. So record will become for this set

      ‘E1’ 1100 15.0787 16.1345
      ‘E1’ ,1100 , 16.084 , 18.045
      temporary Output wll be : E1 1100 15.0787 18.045
        • 1. Re: SQL query to check continious records
          Frank Kulash
          Hi,
          user5698021 wrote:
          With T1 As ( SELECT ‘E1’ as E ,1100 as T , 15.0787 as SM1 , 16.1345 as EM1 from dual union all
          SELECT ‘E1’ ,1100 , 16.084 , 18.045 from dual union all
          SELECT ‘E2’ ,1100 , 15.0787 , 16.1345 from dual union all
          SELECT ‘E2’ ,1100 , 15.0327 , 18.045 from dual union all
          SELECT ‘E3’ ,1100 , 15.0197 , 16.1445 from dual union all
          SELECT ‘E3’ ,1100 , 16.1445 , 18.045 from dual union all
          SELECT ‘E4’ ,1100 , 13.0787 , 14.0743 from dual union all
          SELECT ‘E4’ ,1100 , 14.0743 , 34.0843 from dual union all
          SELECT ‘E4’ ,1100 , 34.0843 , 54.0743 from
          SELECT ‘E4’ ,1110 , 13.0787 , 14.0743 from dual union all
          SELECT ‘E4’ ,1110 , 14.0743 , 34.0843 from dual union all
          SELECT ‘E4’ ,1110 , 34.0843 , 54.0743 from union all
          SELECT ‘E1’ ,1100 , 19.084 , 20.045 from )
          Thanks for posting this. Please test (and, if necessary, correct) your code before you post it. The code above has syntax errors.

          Use only straight single-quotes (CHR (39)) around string literals. That is, don't say:
          SELECT  ‘E1’ , ...
          instead, say:
          SELECT  'E1' , ...
          Main Output should be come as


          E1      1100     15.0787 18.045
          E2 1100 15.0787 16.1345
          E2 1100 15.0327 18.045
          E3 1100 15.0197 18.045
          E4 1100 13.0787 54.0743
          E4 1110 13.0787 54.0743
          E1 1100 19.084 20.045

          could you help me with an sql to get the first output ?


          Logic to derive the above output:

          For a particular set of E and T as long as the EM1 of previous record <=SM1 of next record and SM1 of previous record < = SM2 of next record
          What is sm2?
          then consider it as a continious record. So record will become for this set
          What does "previous" mean in this case? If you look at 2 rows, how can you tell which comes first? Remember, there is no built-in order to rows in a relational database. Are there other significant columns in the table that you haven't shown?
          ‘E1’ 1100 15.0787 16.1345
          ‘E1’ ,1100 , 16.084 , 18.045
          temporary Output wll be : E1 1100 15.0787 18.045
          You can use LEAD or LAG to see when a new group starts, creating a new column with 0 or 1. Use the analytic SUM function on that column to see how many groups have already begun, and then GROUP BY that number; something like this:
          WITH     got_grp_start     AS
          (
               SELECT     e, t, sm1, em1
               ,     order_col
               ,     CASE
                        WHEN  e  = LAG (e)           OVER (ORDER BY order_col)
                        AND       t  = LAG (t)        OVER (ORDER BY order_col)
                        AND   sm1  BETWEEN LAG (sm1) OVER (ORDER BY order_col)
                                    AND     LAG (em1) OVER (ORDER BY order_col)
                        THEN  0
                        ELSE  1
                    END     AS grp_start
               FROM    t1
          )
          ,     got_grp          AS
          (
               SELECT     e, t, sm1, em1
               ,     SUM (grp_start) OVER (ORDER BY order_col)     AS grp
               FROM     got_grp_start
          )
          SELECT       e
          ,       t
          ,       MIN (sm1)     AS start_sm1
          ,       MAX (em1)     AS end_em1
          FROM       got_grp
          GROUP BY  grp, e, t
          ORDER BY  grp
          ;
          I couldn't test this, of course.
          I assume there's another column, order_col, in the table, that determines what "previous" means.

          Edited by: Frank Kulash on Dec 7, 2012 6:15 AM
          • 2. Re: SQL query to check continious records
            Suman Rana
            Try this one...

            WITH T1 AS
                      (SELECT 'E1' AS E, 1100 AS T, 15.0787 AS SM1, 16.1345 AS EM1 FROM DUAL
                      UNION ALL
                      SELECT 'E1', 1100, 16.084, 18.045 FROM DUAL
                      UNION ALL
                      SELECT 'E2', 1100, 15.0787, 16.1345 FROM DUAL
                      UNION ALL
                      SELECT 'E2', 1100, 15.0327, 18.045 FROM DUAL
                      UNION ALL
                      SELECT 'E3', 1100, 15.0197, 16.1445 FROM DUAL
                      UNION ALL
                      SELECT 'E3', 1100, 16.1445, 18.045 FROM DUAL
                      UNION ALL
                      SELECT 'E4', 1100, 13.0787, 14.0743 FROM DUAL
                      UNION ALL
                      SELECT 'E4', 1100, 14.0743, 34.0843 FROM DUAL
                      UNION ALL
                      SELECT 'E4', 1100, 34.0843, 54.0743 FROM DUAL
                      UNION ALL
                      SELECT 'E4', 1110, 13.0787, 14.0743 FROM DUAL
                      UNION ALL
                      SELECT 'E4', 1110, 14.0743, 34.0843 FROM DUAL
                      UNION ALL
                      SELECT 'E4', 1110, 34.0843, 54.0743 FROM DUAL
                      UNION ALL
                      SELECT 'E1', 1100, 19.084, 20.045 FROM DUAL),
                 MainQry AS
                      (     SELECT e, t, sm1, LAG (sm1, 1, 0) OVER (PARTITION BY e, t ORDER BY e, t, sm1) prev_SM1,
                                LEAD (sm1, 1, 0) OVER (PARTITION BY e, t ORDER BY e, t, sm1) next_SM1,
                                EM1, LAG (EM1, 1, 0) OVER (PARTITION BY e, t ORDER BY e, t, EM1) prev_EM1,
                                LEAD (EM1, 1, 0) OVER (PARTITION BY e, t ORDER BY e, t, EM1) next_EM1
                           FROM t1
                      ORDER BY e, t, sm1),
                 SubQry as (SELECT e, t, sm1, em1, CASE WHEN prev_EM1 <= next_SM1     AND prev_SM1 <= next_SM1 THEN 'Y' ELSE 'N' END Include
                                FROM MainQry )
                 Select e, t, sm1, em1 from SubQry WHERE Include = 'Y'



            E T SM1 EM1
            -- ---------- ---------- ----------
            E1 1100 15.0787 16.1345
            E1 1100 16.084 18.045
            E3 1100 15.0197 16.1445
            E4 1100 13.0787 14.0743
            E4 1100 14.0743 34.0843
            E4 1110 13.0787 14.0743
            E4 1110 14.0743 34.0843
            • 3. Re: SQL query to check continious records
              hm
              I found out this:
              With T1 As ( 
              SELECT 'E1' as E ,1100 as T , 15.0787 as SM1 , 16.1345 as EM1 from dual union all
              SELECT 'E1' ,1100 , 16.084 , 18.045 from dual union all
              SELECT 'E2' ,1100 , 15.0787 , 16.1345 from dual union all
              SELECT 'E2' ,1100 , 15.0327 , 18.045 from dual union all
              SELECT 'E3' ,1100 , 15.0197 , 16.1445 from dual union all
              SELECT 'E3' ,1100 , 16.1445 , 18.045 from dual union all
              SELECT 'E4' ,1100 , 13.0787 , 14.0743 from dual union all
              SELECT 'E4' ,1100 , 14.0743 , 34.0843 from dual union all
              SELECT 'E4' ,1100 , 34.0843 , 54.0743 from dual union all
              SELECT 'E4' ,1110 , 13.0787 , 14.0743 from dual union all
              SELECT 'E4' ,1110 , 14.0743 , 34.0843 from dual union all
              SELECT 'E4' ,1110 , 34.0843 , 54.0743 from dual union all
              SELECT 'E1' ,1100 , 19.084 , 20.045 from  dual)
              -- query:
              select  E,T, X1, max(X2) from
              (
              select 
              E, T, least(sm1,em1) X1, to_number(regexp_substr(sys_connect_by_path(greatest(sm1,em1),'/'),'[^/]+')) X2
              from T1
              where connect_by_isleaf=1
              connect by nocycle 
              (prior sm1 between sm1 and em1   ) 
                 and prior E=E and prior T=t
              )
              group by e,t, x1
              order by e,t,x1;
              result:
              E     T       X1         max(X2)
              --------------------------------
              E1    1100    15,0787    18,045
              E1    1100    19,084     20,045
              E2    1100    15,0327    18,045
              E3    1100    15,0197    18,045
              E4    1100    13,0787    54,0743
              E4    1110    13,0787    54,0743
              (I have only one result for E2-1100, because they overlap.)

              Edited by: hm on 07.12.2012 06:28
              • 4. Re: SQL query to check continious records
                hm
                @Suman Rana:

                Where is the result for the combination 'E2', 1100?
                • 5. Re: SQL query to check continious records
                  chris227
                  I assume you intervals are well formed.
                  Otherwises you have to adjust the query accuratly.
                  With T1 As (
                  SELECT 'E1' as E ,1100 as T , 15.0787 as SM1 , 16.1345 as EM1 from dual union all
                  SELECT 'E1' ,1100 , 16.084 , 18.045 from dual union all
                  SELECT 'E2' ,1100 , 15.0787 , 16.1345 from dual union all
                  SELECT 'E2' ,1100 , 15.0327 , 18.045 from dual union all
                  SELECT 'E3' ,1100 , 15.0197 , 16.1445 from dual union all
                  SELECT 'E3' ,1100 , 16.1445 , 18.045 from dual union all
                  SELECT 'E4' ,1100 , 13.0787 , 14.0743 from dual union all
                  SELECT 'E4' ,1100 , 14.0743 , 34.0843 from dual union all
                  SELECT 'E4' ,1100 , 34.0843 , 54.0743 from dual union all
                  SELECT 'E4' ,1110 , 13.0787 , 14.0743 from dual union all
                  SELECT 'E4' ,1110 , 14.0743 , 34.0843 from dual union all
                  SELECT 'E4' ,1110 , 34.0843 , 54.0743 from dual union all
                  SELECT 'E1' ,1100 , 19.084 , 20.045 from dual
                  )
                  , rdata as (
                  select
                   e
                  ,t
                  ,sm1
                  ,em1
                  ,row_number() over (partition by e, t order by e, t, sm1, em1) rn
                  from t1
                  )
                  , connects as (
                  select
                   connect_by_root(rn) root
                  ,e
                  ,t
                  ,sm1
                  ,em1
                  from rdata r
                  start with
                  r.sm1 > nvl((select em1 from rdata where E = r.e and t = r.t and rn = r.rn-1),-1)
                  connect by nocycle
                  sm1 <= prior em1
                  and
                  e = prior e
                  and
                  t = prior t
                  and
                  rn > prior rn
                  )
                  
                  select distinct
                   e
                  ,t
                  ,root
                  ,min(sm1) sm1
                  ,max(em1) me1
                  from connects
                  group by
                  e,t,root
                  order by
                  e,t,sm1,me1
                  
                  E T ROOT SM1 ME1 
                  E1 1100 1 15.0787 18.045 
                  E1 1100 3 19.084 20.045 
                  E2 1100 1 15.0327 18.045 
                  E3 1100 1 15.0197 18.045 
                  E4 1100 1 13.0787 54.0743 
                  E4 1110 1 13.0787 54.0743 
                  Edited by: chris227 on 07.12.2012 04:19
                  • 6. Re: SQL query to check continious records
                    743517
                    I slightly modify my logic.

                    With T1 As (
                    SELECT 'E1' as E ,1100 as T , 15.0787 as SM1 , 16.1345 as EM1 from dual union all
                    SELECT 'E1' ,1100 , 16.084 , 18.045 from dual union all
                    SELECT 'E2' ,1100 , 15.0787 , 16.1345 from dual union all
                    SELECT 'E2' ,1100 , 15.0327 , 18.045 from dual union all
                    SELECT 'E3' ,1100 , 15.0197 , 16.1445 from dual union all
                    SELECT 'E3' ,1100 , 16.1445 , 18.045 from dual union all
                    SELECT 'E4' ,1100 , 13.0787 , 14.0743 from dual union all
                    SELECT 'E4' ,1100 , 14.0743 , 34.0843 from dual union all
                    SELECT 'E4' ,1100 , 34.0843 , 54.0743 from dual union all
                    SELECT 'E4' ,1110 , 13.0787 , 14.0743 from dual union all
                    SELECT 'E4' ,1110 , 14.0743 , 34.0843 from dual union all
                    SELECT 'E4' ,1110 , 34.0843 , 54.0743 from dual union all
                    SELECT 'E1' ,1100 , 19.084 , 20.045 from dual union all
                    SELECT 'E5' ,1100 , 15.084 , 17.084 from dual union all
                    SELECT 'E5' ,1100 , 11.059 , 15.084 from dual union all
                    SELECT 'E6' ,1100 , 15.084 , 17.084 from dual union all
                    SELECT 'E6' ,1100 , 11.059 , 13.054 from dual

                    )


                    Main Output should be come as

                    E1 1100 15.0787 16.1345
                    E2 1100 15.0327 18.045
                    E3 1100 15.0197 18.045
                    E4 1100 13.0787 54.0743
                    E4 1110 13.0787 54.0743
                    E1 1100 19.084 20.045
                    E5 1100 11.059 17.08
                    E6 1100 11.059 13.054
                    E6 1100 15.084 17.084

                    could you help me with an sql to get the first output ?


                    Logic to derive the above output:

                    1)For a particular set of E and T as long as the EM1 of previous record =SM1 of next record then consider two records as a continious record.



                    'E1' 1100 15.0787 16.1345
                    'E1’',1100 , 16.084 , 18.045
                    temporary Output wll be : E1 1100 15.0787 18.045

                    2)If the EM1 for first record > SM1 for the second record then consider the record with lowest SM1. Discard the other record

                    'E2' ,1100 , 15.0787 , 16.1345
                    'E2' ,1100 , 15.0327 , 18.045
                    temporary Output wll be : E2 1100 15.0327 18.045


                    3) If the EM1 for first record < SM1 for the second record then consider both the records
                    • 7. Re: SQL query to check continious records
                      chris227
                      >
                      Main Output should be come as

                      E1 1100 15.0787 16.1345
                      E2 1100 15.0327 18.045
                      E3 1100 15.0197 18.045
                      E4 1100 13.0787 54.0743
                      E4 1110 13.0787 54.0743
                      E1 1100 19.084 20.045
                      E5 1100 11.059 17.08
                      E6 1100 11.059 13.054
                      E6 1100 15.084 17.084

                      could you help me with an sql to get the first output ?
                      This is exactly the output you will got from my query above.
                      Sorry your requirements still are to vague to spent more effort on them at the moment.
                      You got some valid solutions to play around with.
                      Notice, that most solutions given, dont focus on records but rather on min/max of groups.
                      You my try something with case here to get where you want to.