Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2012
    Posts
    3

    Post Unanswered: Grouping Records

    Hi,

    I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:


    Table - Vehicle_Event

    Vehicle_Id____Date_Time______________Speed
    C1____________2012-06-28_10:10:00____5
    C1____________2012-06-28_10:11:00____0
    C1____________2012-06-28_10:12:00____0
    C1____________2012-06-28_10:13:00____4
    C1____________2012-06-28_10:14:00____3
    C1____________2012-06-28_10:15:00____0
    C1____________2012-06-28_10:16:00____0
    C1____________2012-06-28_10:17:00____0
    C1____________2012-06-28_10:18:00____0
    C1____________2012-06-28_10:19:00____2
    C2____________2012-06-28_10:10:00____9
    C2____________2012-06-28_10:11:00____6
    C2____________2012-06-28_10:12:00____0
    C2____________2012-06-28_10:13:00____7
    C2____________2012-06-28_10:14:00____2
    C2____________2012-06-28_10:15:00____0
    C2____________2012-06-28_10:16:00____0
    C2____________2012-06-28_10:17:00____4
    C2____________2012-06-28_10:18:00____1
    C2____________2012-06-28_10:19:00____2



    OUTPUT_Required:

    Vehicle_Id____Date_Time___________________________ __________Speed
    C1____________2012-06-28_10:10:00___________________________5
    C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
    C1____________2012-06-28_10:13:00___________________________4
    C1____________2012-06-28_10:14:00___________________________3
    C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0
    C1____________2012-06-28_10:19:00___________________________2
    C2____________2012-06-28_10:10:00___________________________9
    C2____________2012-06-28_10:11:00___________________________6
    C2____________2012-06-28_10:12:00___________________________0
    C2____________2012-06-28_10:13:00___________________________7
    C2____________2012-06-28_10:14:00___________________________2
    C2____________2012-06-28_10:15:00_to_2012-06-28_10:16:00____0
    C2____________2012-06-28_10:17:00___________________________4
    C2____________2012-06-28_10:18:00___________________________1
    C2____________2012-06-28_10:19:00___________________________2






    I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.

    Can someone please help me with the query?

    Thanks in advance.

    Regards,
    Subhra.
    Last edited by sjmajumder; 06-28-12 at 08:56.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thanks for the challenge.
    Code:
    set nocount on
    
    declare	@SampleData table (Vehicle_Id char(2), Date_Time DateTime, Speed int)
    insert into @SampleData select 'C1', '2012-06-28 10:10:00', 5
    insert into @SampleData select 'C1', '2012-06-28 10:11:00', 0
    insert into @SampleData select 'C1', '2012-06-28 10:12:00', 0
    insert into @SampleData select 'C1', '2012-06-28 10:13:00', 4
    insert into @SampleData select 'C1', '2012-06-28 10:14:00', 3
    insert into @SampleData select 'C1', '2012-06-28 10:15:00', 0
    insert into @SampleData select 'C1', '2012-06-28 10:16:00', 0
    insert into @SampleData select 'C1', '2012-06-28 10:17:00', 0
    insert into @SampleData select 'C1', '2012-06-28 10:18:00', 0
    insert into @SampleData select 'C1', '2012-06-28 10:19:00', 2
    insert into @SampleData select 'C2', '2012-06-28 10:10:00', 9
    insert into @SampleData select 'C2', '2012-06-28 10:11:00', 6
    insert into @SampleData select 'C2', '2012-06-28 10:12:00', 0
    insert into @SampleData select 'C2', '2012-06-28 10:13:00', 7
    insert into @SampleData select 'C2', '2012-06-28 10:14:00', 2
    insert into @SampleData select 'C2', '2012-06-28 10:15:00', 0
    insert into @SampleData select 'C2', '2012-06-28 10:16:00', 0
    insert into @SampleData select 'C2', '2012-06-28 10:17:00', 4
    insert into @SampleData select 'C2', '2012-06-28 10:18:00', 1
    insert into @SampleData select 'C2', '2012-06-28 10:19:00', 2
    insert into @SampleData select 'C2', '2012-06-28 10:22:00', 0 --Added this zero record as an edge case.
    
    ;with	OrderedRecords as --Start by ordering the records by vehicle and time
    			(select	Vehicle_Id,
    					Date_Time,
    					Speed,
    					ROW_NUMBER() over (partition by Vehicle_Id order by Date_Time) as RowNum
    			from	@SampleData),
    		CTE as
    			(--Get the first records
    			select	FirstSpeedRecords.Vehicle_Id,
    					FirstSpeedRecords.Date_Time as FromTime,
    					SecondSpeedRecords.Date_Time as ToTime,
    					FirstSpeedRecords.Speed,
    					Coalesce(SecondSpeedRecords.RowNum, FirstSpeedRecords.RowNum) as RowNum
    			from	OrderedRecords as FirstSpeedRecords
    					left outer join OrderedRecords as SecondSpeedRecords
    						on FirstSpeedRecords.Vehicle_Id = SecondSpeedRecords.Vehicle_Id
    						and FirstSpeedRecords.RowNum = SecondSpeedRecords.RowNum - 1
    						and FirstSpeedRecords.Speed = 0
    						and SecondSpeedRecords.Speed = 0
    					left outer join OrderedRecords as PriorSpeedRecords
    						on FirstSpeedRecords.Vehicle_Id = PriorSpeedRecords.Vehicle_Id
    						and FirstSpeedRecords.RowNum = PriorSpeedRecords.RowNum + 1
    						and FirstSpeedRecords.Speed = 0
    						and PriorSpeedRecords.Speed = 0
    			where	PriorSpeedRecords.RowNum is null
    			union all
    			select	CTE.Vehicle_Id,
    					CTE.FromTime,
    					coalesce(NextSpeedRecords.Date_time, CTE.ToTime) as ToTime,
    					CTE.Speed,
    					Coalesce(NextSpeedRecords.RowNum, CTE.RowNum) as RowNum
    			from	CTE
    					inner join OrderedRecords as NextSpeedRecords
    						on CTE.Vehicle_Id = NextSpeedRecords.Vehicle_Id
    						and CTE.RowNum = NextSpeedRecords.RowNum - 1
    						and CTE.Speed = 0
    						and NextSpeedRecords.Speed = 0)
    select	Vehicle_Id,
    		FromTime,
    		MAX(ToTime) as ToTime,
    		Speed
    from	CTE
    group by Vehicle_Id,
    		FromTime,
    		Speed
    order by Vehicle_Id,
    		FromTime,
    		Speed
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2012
    Posts
    3
    Thanks a lot blindman.

  4. #4
    Join Date
    Jun 2012
    Posts
    3
    But this is not working for more than 100 records. Any solution?

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Look up MAXRECURSION in the SQL Server documentation.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    I do not know how it will be the performance but try this:

    Code:
    with 
        CTE_All as
        (
            select 
                *, 
                ROW_NUMBER() OVER(PARTITION BY Vehicle_Id ORDER BY Date_Time) as RowNumA
            from @SampleData
        ),
        
        CTE_F as
        (
            select 
                c.Vehicle_Id, 
                c.Date_Time, 
                c.Speed,
                c.RowNumA,
                ROW_NUMBER() OVER(PARTITION BY c.Vehicle_Id ORDER BY c.RowNumA) as RowNumF
            from CTE_ALL as c
            left join CTE_ALL as a on (a.Vehicle_Id = c.Vehicle_Id) and 
                                      (a.RowNumA = c.RowNumA - 1)
            where (c.Speed <> 0) or
                  ( (a.Speed <> 0) or (a.Speed is null) )
        )
    
    select 
        f.Vehicle_Id,
        f.Date_Time,
        case when f.Speed = 0 
            then (select MAX(a.Date_Time)
                  from CTE_ALL as a 
                  where (a.Vehicle_Id = f.Vehicle_Id) and
                        (a.RowNumA > f.RowNumA) and
                        (a.RowNumA < n.RowNumA))
        end as Date_Time_To,
        f.Speed
    from CTE_F as f
    left join CTE_F as n on (n.Vehicle_Id = f.Vehicle_Id) and
                            (n.RowNumF = f.RowNumF + 1)
    Hope it's useful.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT Vehicle_Id
         , MIN(Date_Time)
           + CASE MIN(Date_Time)
             WHEN MAX(Date_Time) THEN
                  ''
             ELSE ' to ' + MAX(Date_Time)
             END      AS Date_Time
         , MAX(Speed) AS Speed
     FROM (SELECT s.*
                , CASE Speed
                  WHEN 0 THEN
                       DATEDIFF(mi , '1900-01-01 00:00:00.0000000' , Date_Time)
                  ELSE 0
                  END
                - ROW_NUMBER()
                     OVER( PARTITION BY Vehicle_Id
                               ORDER BY CASE Speed
                                        WHEN 0 THEN
                                             Date_Time
                                        END  NULLS LAST
                         )
                  AS diff_group
            FROM  SampleData s
          )
     GROUP BY
           Vehicle_Id
         , diff_group
     ORDER BY
           Vehicle_Id
         , Date_Time
    ;

Tags for this Thread

Posting Permissions

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