SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Overlap Distances in 2 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JacekK
Starting Member

USA
14 Posts

Posted - 07/09/2010 :  16:03:12  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 07/10/2010 :  05:21:58  Show Profile  Reply with Quote

; 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



KH
Time is always against us

Go to Top of Page

JacekK
Starting Member

USA
14 Posts

Posted - 07/14/2010 :  16:38:27  Show Profile  Reply with Quote
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!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/14/2010 :  17:57:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
;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



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/14/2010 :  21:32:25  Show Profile  Reply with Quote

; 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



KH
Time is always against us

Go to Top of Page

JacekK
Starting Member

USA
14 Posts

Posted - 07/21/2010 :  11:45:15  Show Profile  Reply with Quote
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')
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/22/2010 :  20:32:57  Show Profile  Reply with Quote

; 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



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000