I have a table (tblVisits) as given below in SQL Server 2000:

PlaceName|AtDate
A|01-Jan-2010 11:30
A|01-Jan-2010 11:35
A|01-Jan-2010 11:40
A|01-Jan-2010 11:45
A|01-Jan-2010 11:50
B|01-Jan-2010 11:55
B|01-Jan-2010 12:00
B|01-Jan-2010 12:05
B|01-Jan-2010 12:10
A|01-Jan-2010 12:15
A|01-Jan-2010 12:20
A|01-Jan-2010 12:25

I need to group the records so that I get the result as shown below
Correct Result
PlaceName|FromDate|ToDate
A|01-Jan-2010 11:30|01-Jan-2010 11:50
B|01-Jan-2010 11:55|01-Jan-2010 12:10
A|01-Jan-2010 12:15|01-Jan-2010 12:25

How can I do this? I DON'T WANT the result shown below, which is what I am getting now
Wrong Result
PlaceName|FromDate|ToDate
A|01-Jan-2010 11:30|01-Jan-2010 12:25
B|01-Jan-2010 11:55|01-Jan-2010 12:10

Thanks So Much

Hi,

I tried dense ranking but that too groups in an order not required. Also, I thought I will explain the scenario. A vehicle moves across places sending Lat/Lon values at various times. If it moves along 'A', then 'B' and then 'A' again, we need to know how long it was at 'A', then 'B' and then again 'A'.

Thus we need get,
Correct Result
PlaceName|FromDate|ToDate
A|01-Jan-2010 11:30|01-Jan-2010 11:50 (R1)
B|01-Jan-2010 11:55|01-Jan-2010 12:10 (R2)
A|01-Jan-2010 12:15|01-Jan-2010 12:25 (R3)

Where
PlaceName|AtDate
A|01-Jan-2010 11:30 (R1)
A|01-Jan-2010 11:35
A|01-Jan-2010 11:40
A|01-Jan-2010 11:45
A|01-Jan-2010 11:50 (R1)
B|01-Jan-2010 11:55 (R2)
B|01-Jan-2010 12:00
B|01-Jan-2010 12:05
B|01-Jan-2010 12:10 (R2)
A|01-Jan-2010 12:15 (R3)
A|01-Jan-2010 12:20
A|01-Jan-2010 12:25 (R3)

Thanks Again

I would write a query with two derived table queries (sub-queries). One sub-query would find the minimum date grouped by place name. The second sub-query would find the maximum date grouped by place name. The main query would join both these subqueries on place name and output the results.

Something like this.

I'm typing this raw--not at a SQL client--so I haven't checked this for syntax or result.

Code:

select  MN.PlaceName
        ,MN.MinimumAtDate
	,MX.MaximumAtDate
inner
join	(
	select	PlaceName
		,min(AtDate) MinimumAtDate
	from	DataTable
	group
	by	PlaceName
	) MN
inner
join	(
	select	PlaceName
		,min(AtDate) MinimumAtDate
	from	DataTable
	group
	by	PlaceName
	) MX on
		MX.PlaceName=MN.PlaceName
order
by	MN.PlaceName

Correction