Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Unanswered: Grouping without breaks

    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

  2. #2
    Join Date
    Feb 2010
    Posts
    2

    Update

    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

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    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
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Correction

    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
    		,max(AtDate) MaximumAtDate
    	from	DataTable
    	group
    	by	PlaceName
    	) MX on
    		MX.PlaceName=MN.PlaceName
    order
    by	MN.PlaceName
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •