comparing range of values with another range of values mysql

Question

I have a table (shrewd_db.threshold) in MySQL database where I have executed following code

select  id_indicator, id_threshold, activation_begin_value, activation_end_value 
from shrewd_db.threshold
where active =1
group by id_indicator, id_threshold;

My output is as follows:

 id_indicator   id_threshold    activation_begin_value  activation_end_value 

   2                5                   4                 58
   2                6                   3                  3
   2                7                   1                  2
   2                8                   0                  0
   4               13                   0                4.5
   4               15                 4.1                5.5
   4               16                5.51                  6
   4               17                6.01                100

Each id_indicator has four range of id_threshold.

Now for a data validation check I need to find out each indicator id_threshold (activation_begin_value and activation_end_value) not fall into another threshold.

My desired output should be like:

 id_indicator   id_threshold    activation_begin_value  activation_end_value 


   4               13                   0                4.5
   4               15                 4.1                5.5

where id_indicator (4) two id_threshold(13,15) values falls each other. Mainly identify threshold must not overlap

where id_indicator (4) two id_threshold(13,15) values falls each other => This doesn't make sense to. Can you elaborate why do you need only these 2 rows in output and not any other rows? — Utsav
– Utsav, Commented Dec 22, 2016 at 10:49
example id indicator 4 has value 4.2, if I need to find out value 4.2 fall into which threshold my out put giving me above result which fall in to threshold 13 and 15. generally each threshold should have unique range of value that is why for data validation check I need to find all those anomalies and resolve the threshold problem — Sumon
– Sumon, Commented Dec 22, 2016 at 10:57

Stephan Lechner · Accepted Answer · 2016-12-28 20:57:54Z

1

I think you mean that activation periods must not overlap, right?

Then a query checking this would look as follows:

select distinct t1.* from threshold t1, threshold t2 where
  t1.id_indicator = t2.id_indicator and
  t1.id_threshold <> t2.id_threshold and
    (  t1.activation_begin_value BETWEEN t2.activation_begin_value and 
       t2.activation_end_value
    OR t1.activation_end_value BETWEEN t2.activation_begin_value and 
       t2.activation_end_value
    )

The query is tested with the following schema:

CREATE TABLE threshold
    (`id_indicator` int, `id_threshold` int, `activation_begin_value` numeric(5,2), `activation_end_value` numeric(5,2))
;

INSERT INTO threshold
    (`id_indicator`, `id_threshold`, `activation_begin_value`, `activation_end_value`)
VALUES
    (2, 5, 4, 58),
    (2, 6, 3, 3),
    (2, 7, 1, 2),
    (2, 8, 0, 0),
    (4, 13, 0, 4.5),
    (4, 15, 4.1, 5.5),
    (4, 16, 5.51, 6),
    (4, 17, 6.01, 100)
;

And yields the following results:

id_indicator|id_threshold|activation_begin_value | activation_end_value
------------|------------|-----------------------|---------------------
           4|          15|                    4.1|                  5.5
           4|          13|                      0|                  4.5

edited Dec 28, 2016 at 20:57

answered Dec 22, 2016 at 11:59

Stephan Lechner

35.2k4 gold badges38 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sumon Over a year ago

i am getting an error while running your code in "OR" area

Stephan Lechner Over a year ago

hm - I've actually tested the query (see edited answer). Maybe your schema is different from the one that I derived from your sample data?

Satish · Accepted Answer · 2016-12-22 10:59:32Z

0

Your given Query - for which you are getting output - must be throwing error

It cab be - select id_indicator, id_threshold, min(activation_begin_value), Max (activation_end_value) from shrewd_db.threshold where active =1 group by id_indicator, id_threshold;

If you correct this and take this output in some temp table and then write Self Join based activation_begin_value , activation_end_value I think you may get your result.

Else write cursor on the above output and compare row by row...if it's one time job and Performance is not issue..

answered Dec 22, 2016 at 10:59

Satish

562 bronze badges

1 Comment

Sumon Over a year ago

could you help me how to write cursor because it is one time code for data validation and performance is not going to be an issue

Collectives™ on Stack Overflow

comparing range of values with another range of values mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related