How do I merge two interval tables

Question

0.00/5 (No votes)

See more:

Hi All
I need help with sql query to merge two interval tables. I have been struggling with it for a week now.

I have two tables

Table 1

id	    xfrom	xto	    category
AX001	0	    6.05	GOOD
AX001	6.05	6.25	BAD
AX001	6.25	17.67	GOOD
AX001	17.67	31.5	GOOD

Table 2

id	    xfrom	xto	    weight
AX001	0	    1	    3640
AX001	1	    2	    3190
AX001	2	    3	    5790
AX001	3	    4	    3020
AX001	4	    5	    3450
AX001	5	    5.6	    3220
AX001	5.6	    6	    2510
AX001	6	    6.3	    2260
AX001	6.3	    7	    2610
AX001	7	    8	    2340
AX001	8	    9	    2880
AX001	9	    9.65	3380
AX001	9.65	10.4	2480
AX001	10.4	11	    2570
AX001	11	    12	    2050
AX001	12	    12.65	2070
AX001	12.65	13.5	1910
AX001	13.5	14	    2170
AX001	14	    15	    2290
AX001	15	    16	    1840
AX001	16	    17	    2340
AX001	17	    17.67	2160

I want the merged table to be

Merged table

id	    xfrom	xto	    weight	category
AX001	0.00	1.00	3640	GOOD
AX001	1.00	2.00	3190	GOOD
AX001	2.00	3.00	5790	GOOD
AX001	3.00	4.00	3020	GOOD
AX001	4.00	5.00	3450	GOOD
AX001	5.00	5.60	3220	GOOD
AX001	5.60	6.00	2510	GOOD
AX001	6.00	6.05	2260	GOOD
AX001	6.05	6.25	2260	BAD
AX001	6.25	6.30	2260	GOOD
AX001	6.30	7.00	2610	GOOD
AX001	7.00	8.00	2340	GOOD
AX001	8.00	9.00	2880	GOOD
AX001	9.00	9.65	3380	GOOD
AX001	9.65	10.40	2480	GOOD
AX001	10.40	11.00	2570	GOOD
AX001	11.00	12.00	2050	GOOD
AX001	12.00	12.65	2070	GOOD
AX001	12.65	13.50	1910	GOOD
AX001	13.50	14.00	2170	GOOD
AX001	14.00	15.00	2290	GOOD
AX001	15.00	16.00	1840	GOOD
AX001	16.00	17.00	2340	GOOD
AX001	17.00	17.67	2160	GOOD

I want to merge the two tables into one table as shown above

What I have tried:

select * from
(
    select id,depth,
    LAG(depth,1,0) OVER (ORDER BY depth) As [xfrom],
    depth As [xto],[weight],category
    from
    (
        select distinct 
        g.id,g.depth,
        t2.[weight],t1.category
        from (select id,xfrom as depth from t1 where id = 'AX001'
                union
                select id,xto from t2 where id = 'AX001') g
        join t1 on g.depth>=t1.xfrom and g.depth=t2.xfrom and g.depth[From]

and I get the following results

id	    xfrom	xto	    weight	category
AX001	0.00	1.00	3190.00	GOOD
AX001	1.00	2.00	3190.00	GOOD
AX001	2.00	3.00	3020.00	GOOD
AX001	3.00	4.00	3020.00	GOOD
AX001	4.00	5.00	3220.00	GOOD
AX001	5.00	5.60	2510.00	GOOD
AX001	5.60	6.00	2260.00	GOOD
AX001	6.00	6.05	2260.00	BAD
AX001	6.05	6.25	2260.00	BAD
AX001	6.25	6.30	2260.00	GOOD
AX001	6.30	7.00	2340.00	GOOD
AX001	7.00	8.00	2340.00	GOOD
AX001	8.00	9.00	2880.00	GOOD
AX001	9.00	9.65	2480.00	GOOD
AX001	9.65	10.40	2480.00	GOOD
AX001	10.40	11.00	2050.00	GOOD
AX001	11.00	12.00	2050.00	GOOD
AX001	12.00	12.65	1910.00	GOOD
AX001	12.65	13.50	1910.00	GOOD
AX001	13.50	14.00	2170.00	GOOD
AX001	14.00	15.00	1840.00	GOOD
AX001	15.00	16.00	1840.00	GOOD
AX001	16.00	17.00	2160.00	GOOD
AX001	17.00	17.67	2160.00	GOOD

the problem is that from 6.05 to 6.25 is supposed to be GOOD what I'm I missing?

Posted 21-Dec-21 5:15am

wizy@2020

Updated 21-Dec-21 8:59am

v4

Add a Solution

Comments

OriginalGriff 21-Dec-21 11:26am

And?
What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gerry Schmitz · Answer 1 · 2021-12-21T06:19:00

Solution 1

Your ranges overlap; that's a problem; the end point of one interval is the same as the start of another.

And you don't have to "join"; it's a simple "CASE";

xto <= 15 GOOD
xto <= 15.32 BAD
xto <= 15.47 MODERATE
else BAD

Posted 21-Dec-21 6:19am

Gerry Schmitz