3
How do I merge two interval tables
source link: https://www.codeproject.com/Questions/5320568/How-do-I-merge-two-interval-tables
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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
Copy Code
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
Copy Code
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
Copy Code
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:
Copy Code
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
Copy Code
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?
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK