Overlap Distances in 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Overlap Distances in 2 tables

Author

Topic

JacekK
Starting Member

14 Posts

Posted - 2010-07-09 : 16:03:12

I have 2 tables with "from" and "to" distances. Each table represents a different condition within these distances. I need to combine these tables based on the distance, i.e. I need to create on table that would be "segmented" by "from" and "to" distances.

Below is an example. I am trying to arrive at @t_results from the two top tables. Thanks!

declare @t1 table ( from_mile numeric(10), to_mile numeric(20), value1 int)
insert into @t1 values (0,150,1)
insert into @t1 values (150,300,2)
insert into @t1 values (300,450,3)

declare @t2 table ( from_mile numeric(10), to_mile numeric(20), value2 char)
insert into @t2 values (50,75,'A')
insert into @t2 values (75,90,'B')
insert into @t2 values (90,160,'C')
insert into @t2 values (160,180,'D')
insert into @t2 values (180,350,'E')

declare @t_results table ( from_mile numeric(10), to_mile numeric(20), value1 int, value2 char)
insert into @t_results values (0,50,1,'')
insert into @t_results values (50,75,1,'A')
insert into @t_results values (75,90,1,'B')
insert into @t_results values (90,150,1,'C')
insert into @t_results values (150,160,2,'C')
insert into @t_results values (160,180,2,'D')
insert into @t_results values (180,300,2,'E')
insert into @t_results values (300,350,3,'E')
insert into @t_results values (350,450,3,'E')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-10 : 05:21:58

[code]
; with
miles
as
(
select mile, value, row_no = dense_rank() over (order by mile)
from
(
select mile = from_mile, value = '' from @t1
union all
select mile = from_mile, value = value2 from @t2
union all
select mile = to_mile, value = NULL from @t1
union all
select mile = to_mile, value = value2 from @t2
) m
)
select from_mile = m1.mile,
to_mile = m2.mile,
max(coalesce(m1.value, m2.value))
from miles m1
inner join miles m2 on m1.row_no = m2.row_no - 1
group by m1.mile, m2.mile
[/code]

KH
[spoiler]Time is always against us[/spoiler]

JacekK
Starting Member

14 Posts

Posted - 2010-07-14 : 16:38:27

Thanks KH!

Unfortunately, this code is not 100% accurate. In your solution segment from 150 to 160 miles has "D" for a value, but it should be "C".

Overlap of both rows:

@t1 values (150,300,2)
@t2 values (90,160,'C')

creates

@t_results values (150,160,2,'C')

Also, I need to preserve both value1 and value2 from both tables. Is there an easy "fix" to your code?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-14 : 17:57:38

[code];WITH cteMiles(Mile)
AS (
SELECT from_mile
FROM @t1

UNION

SELECT to_mile
FROM @t1

UNION

SELECT from_mile
FROM @t2

UNION

SELECT to_mile
FROM @t2
), cteRank(Mile, Rnk)
AS (
SELECT Mile,
ROW_NUMBER() OVER (ORDER BY Mile) AS Rnk
FROM cteMiles
), cteSource(from_mile, to_mile)
AS (
SELECT r2.Mile,
r1.Mile
FROM cteRank AS r1
INNER JOIN cteRank AS r2 ON r2.Rnk = r1.Rnk - 1
)
SELECT s.from_mile,
s.to_mile,
t1.value1,
ISNULL(t2.value2, '') AS value2
FROM cteSource AS s
LEFT JOIN @t1 AS t1 ON t1.from_mile < s.to_mile
AND t1.to_mile > s.from_mile
LEFT JOIN @t2 AS t2 ON t2.from_mile < s.to_mile
AND t2.to_mile > s.from_mile[/code]

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-14 : 21:32:25

[code]
; with
miles
as
(
select mile, value1, value2, row_no = dense_rank() over (order by mile)
from
(
select mile = from_mile, value1, value2 = NULL from @t1
union all
select mile = from_mile, value1 = NULL, value2 from @t2
union all
select mile = to_mile, value1, value2 = NULL from @t1
union all
select mile = to_mile, value1 = NULL, value2 from @t2
) m
)
select m.from_mile, m.to_mile,
value1 = isnull(v1.value1, ''),
value2 = isnull(v2.value2, '')
from (
select from_mile = m1.mile,
to_mile = m2.mile
from miles m1
inner join miles m2 on m1.row_no = m2.row_no - 1
group by m1.mile, m2.mile
) m
outer apply
(
select top 1 value1
from @t1 t1
where t1.from_mile <= m.from_mile
and t1.to_mile >= m.from_mile
order by t1.from_mile
) v1
outer apply
(
select top 1 value2
from @t2 t2
where t2.from_mile <= m.from_mile
and t2.to_mile >= m.from_mile
order by t2.from_mile
) v2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

JacekK
Starting Member

14 Posts

Posted - 2010-07-21 : 11:45:15

Thanks a lot! This is a big help. What if we add 1 more level of complexity and we want to partition the results by road name. See below:

Thanks!

--Table1
declare @t1 table ( from_mile numeric(10), to_mile numeric(20), road_name varach(10), value1 int)
insert into @t1 values (0,150,'Main',1)
insert into @t1 values (150,300,'Main',2)
insert into @t1 values (300,450,'Main',3)
insert into @t1 values (0,50,'NotMain',2)
insert into @t1 values (50,125,'NotMain',3)

--Table2
declare @t2 table ( from_mile numeric(10), to_mile numeric(20), road_name varach(10),value2 char)
insert into @t2 values (50,75,'Main','A')
insert into @t2 values (75,90,'Main','B')
insert into @t2 values (90,160,'Main','C')
insert into @t2 values (160,180,'Main','D')
insert into @t2 values (180,350,'Main','E')
insert into @t2 values (0,100,'NotMain','D')
insert into @t2 values (100,250,'NotMain','E')

--Results table
declare @t_results table ( from_mile numeric(10), to_mile numeric(20), value1 int, value2 char)
insert into @t_results values (0,50,'Main',1,'')
insert into @t_results values (50,75,'Main',1,'A')
insert into @t_results values (75,90,'Main',1,'B')
insert into @t_results values (90,150,'Main',1,'C')
insert into @t_results values (150,160,'Main',2,'C')
insert into @t_results values (160,180,'Main',2,'D')
insert into @t_results values (180,300,'Main',2,'E')
insert into @t_results values (300,350,'Main',3,'E')
insert into @t_results values (350,450,'Main',3,'E')
insert into @t_results values (0,50,'NotMain',2,'D')
insert into @t_results values (50,100,'NotMain',3,'D')
insert into @t_results values (100,125,'NotMain',3,'E')
insert into @t_results values (125,250,'NotMain',3,'E')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-22 : 20:32:57

[code]
; with
miles
as
(
select mile, road_name, value1, value2, row_no = dense_rank() over (partition by road_name order by mile)
from
(
select mile = from_mile, road_name, value1, value2 = NULL from @t1
union all
select mile = from_mile, road_name, value1 = NULL, value2 from @t2
union all
select mile = to_mile, road_name, value1, value2 = NULL from @t1
union all
select mile = to_mile, road_name, value1 = NULL, value2 from @t2
) m
)
select m.from_mile, m.to_mile, m.road_name,
value1 = isnull(v1.value1, ''),
value2 = isnull(v2.value2, '')
from (
select from_mile = m1.mile,
to_mile = m2.mile,
m1.road_name
from miles m1
inner join miles m2 on m1.road_name = m2.road_name
and m1.row_no = m2.row_no - 1
group by m1.mile, m2.mile, m1.road_name
) m
outer apply
(
select top 1 value1
from @t1 t1
where t1.road_name = m.road_name
and t1.from_mile <= m.from_mile
and t1.to_mile >= m.from_mile
order by t1.from_mile desc
) v1
outer apply
(
select top 1 value2
from @t2 t2
where t2.road_name = m.road_name
and t2.from_mile <= m.from_mile
and t2.to_mile >= m.from_mile
order by t2.from_mile desc
) v2
order by road_name
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources