how to find the beginning and the end

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 2000 Forums

SQL Server Development (2000)

how to find the beginning and the end

Author

Topic

donar
Starting Member

22 Posts

Posted - 2004-10-22 : 14:28:18

I am now trying to solve this problem:
ID NTS TRACT
556 21A/16C 85
556 21A/16C 86
556 21A/16C 107
556 21A/16C 108
556 21H/01B 11
557 21A/16C 87
557 21A/16C 88
557 21A/16C 89
557 21A/16C 90
557 21A/16C 91
find the beginning tract and end_tract , so the table can look like this
556 21A/16C 85 86
556 21A/16C 107 108
556 21H/01B 11 11
557 21A/16C 87 91

Do i have to write some program to solve this?
Thanks!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-22 : 14:40:54

something like this ??:

select id, nts, min(tract) st_tract , max(tract) ed_tract
from t t1
group by id,nts
order by id,nts

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-22 : 14:55:17

This should do it with out temp tables and such... but it may be cumbersome...


Declare @myTable table (colA int, colB varchar(10), colC int)
Insert Into @myTable
Select 556, '21A/16C', 85
Union Select 556, '21A/16C', 86
Union Select 556, '21A/16C', 107
Union Select 556, '21A/16C', 108
Union Select 556, '21H/01B', 11
Union Select 557, '21A/16C', 87
Union Select 557, '21A/16C', 88
Union Select 557, '21A/16C', 89
Union Select 557, '21A/16C', 90
Union Select 557, '21A/16C', 91

Select
	colA, colB, 
	colCS = min(colCS),
	colCE
From
	(
	Select
		colA, colB, 
		colCS,
		colCE = max(colCE)
	From
		(
		Select A.colA, A.colB, colCS = A.colC, colCE = B.colC
		From @myTable A
		Left Join @myTable B
		On A.colA = B.colA
		and A.colB = B.colB
		Where abs(A.colC-B.colC)+1 = (Select count(distinct colC) from @myTable Where colA = A.colA and colB = A.colB and colC between A.colC and B.colC)
		) Z
	Group By colA, colB, colCS
	) A
Group By colA, colB, colCE

Corey

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-22 : 15:13:02

[code]select t.id
,t.nts
,t.tract Beg_Tract
,min(t1.tract) End_Tract
from MyTable t
inner join
MyTable t1 on t.id=t1.id and
t.nts=t1.nts and
t.tract <=t1.tract
where not exists
(select *
from MyTable t2
where t2.id=t.id and
t2.nts=t.nts and
t2.tract =t.tract -1)
and not exists
(select *
from MyTable t3
where t3.id=t1.id and
t3.nts=t1.nts and
t3.tract =t1.tract +1)
group by t.id,t.nts,t.tract [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources