7 Replies Latest reply on Dec 7, 2012 3:13 PM by chris227

SQL query to check continious records

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
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’ , ...``
``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
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
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
@Suman Rana:

Where is the result for the combination 'E2', 1100?
• 5. Re: SQL query to check continious records
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
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
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
>
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.