Click here to Skip to main content
14,698,201 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hey guys,

Background
I am working on making one of our database MUCH more efficient. The largest table has 3M rows with some text columns containing one of two values >_<

So I have taken out the text, performed some normalization and de-normalization steps, added plenty of indexes where required and it has made it about 3K% to 6K% more efficient. Woot. "Quick quite" is no longer a misnomer, Bosses all impressed, customers very happy etc...

So now they want me to take it another step >_<

Anyway, enough background. Here's the problem

Problem

All of our products have an EAC range (fromcon and tocon). Based on several other criteria, I need to find where these are overlapping or adjacent so I can reduce the total number of records.

I.E:
TableId, FromCon, ToCon, OtherCrieria
1      , 0      , 100  , 1
2      , 101    , 200  , 1
3      , 200    , 400  , 1
4      , 401    , 600  , 2
5      , 601    , 800  , 1


So above there are the first three rows that are adjacent or overlap with the same criteria, another row that is adjacent but the criteria don't match, and the last row with matching criteria but the range is not adjacent to the other matches. The results should look as below:
TableId, FromCon, ToCon, OtherCrieria
1 (min), 0      , 400  , 1
4      , 401    , 600  , 2
5      , 601    , 800  , 1


So how could I go about doing something like this?

Thanks
Andy

What I have tried:

Not even sure where to start so here is some test data instead
select * from (
	select 1 as id, 0 as fromcon, 100 as tocon, 1 as conditions
	union select 2, 101, 200, 1
	union select 3, 200, 400, 1
	union select 4, 401, 600, 2
	union select 5, 601, 800, 1
    
	union select 6, 100, 200, 3
	union select 7, 200, 400, 4
    union select 8, 75, 150, 3
    union select 9, 50, 350, 3
    ) as test
Posted
Updated 24-Nov-16 9:45am

1 solution

   
v2
Comments
Andy Lanng 25-Nov-16 5:49am
   
Awesome! Great info and now I know the name of the problem I can extend the research myself if I need to. Thanks ^_^
Maciej Los 25-Nov-16 5:50am
   
You're very welcome, Andy.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900