none
How to group the records

    Question

  • Hi All,

    I have the following records, I just want to group them:

    Id, Time, Category, relationId, GroupId,

    1, 10:00, StartDate, 1, Null

    2, 11:00, EndDate, 1, Null

    3, 12:00, StartDate, 3, Null

    4, 13:00, EndDate, 3, Null

    5,13:10, StartDate,5, Null

    6, 13:20, EndDate, 5, Null

    7, 16:00, StartDate,7, Null

    8, 17:00, EndDate,8, Null

    What I want to do is update the GroupId for the Records.

    It is very clear 2 records as a couple with StartDate and EndDate, and we can use relationId to get it.

    The logic to update the GroupId is very hard to describe, but I can take one example:

    If the start date of one relationID group minus the previous relation group's end date less than 160 minutes, then they belong to group, we can update them with one group.

    So for the above example: The records from 1 to 6 should be the same group, 7 and 8 are in the same group.

    How to write this SQL?

    Thanks.

    Monday, May 20, 2013 5:57 AM

Answers

  • Are you looking for the below: Note: If you have only 2 groups, it will work. But for multiple group, you need to work in different way. Please clarify.

    Create table #t(Id int, DTime Datetime, Category Varchar(50), relationId int, GroupId int)
    insert into #t Values(	1, '2012-1-1 10:00', 'StartDate', 1, Null)
    insert into #t Values(	2, '2012-1-1 11:00', 'EndDate', 1, Null)
    insert into #t Values(	3, '2012-1-1 12:00', 'StartDate', 3, Null)
    insert into #t Values(	4, '2012-1-1 13:00', 'EndDate', 3, Null)
    insert into #t Values(	5, '2012-1-1 13:10', 'StartDate',5, Null)
    insert into #t Values(	6, '2012-1-1 13:20', 'EndDate', 5, Null)
    insert into #t Values(	7, '2012-1-1 16:00', 'StartDate',7, Null)
    insert into #t Values(	8, '2012-1-1 17:00', 'EndDate',7, Null)
    ;With cte
    As
    ( 
    	Select A.Id 'I1',B.Id 'I2',A.DTime 'D1', B.DTime 'D2', A.Category 'C1',B.Category 'C2',
    	A.relationId 'R1',B.relationId 'R2',A.GroupId 'G1',B.GroupId 'G2' 
    	From #t A
    	Inner Join #t B on B.Id = A.Id+1
    )
    update A Set GroupId  =  Case When ID <(
    Select Max(i1) From cte Where DATEDIFF(MINUTE,D2,D1) <160
    ) Then  1 
    Else 2
    End
    From #t A
    Select * From #t
    Drop table #t


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, May 20, 2013 7:27 AM

All replies

  • Your time ontains date also? if not, how do we interpret it the day and find out the difference?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, May 20, 2013 6:08 AM
  • Yes, it contains, Sorry I just Listed the Timeslot.

    Id, Time, Category, relationId, GroupId,

    1, 2012-1-1 10:00, StartDate, 1, Null

    2, 2012-1-1 11:00, EndDate, 1, Null

    3, 2012-1-1 12:00, StartDate, 3, Null

    4, 2012-1-1 13:00, EndDate, 3, Null

    5,2012-1-1 13:10, StartDate,5, Null

    6, 2012-1-1 13:20, EndDate, 5, Null

    7,2012-1-1  16:00, StartDate,7, Null

    8, 2012-1-1 17:00, EndDate,8, Null

    Monday, May 20, 2013 6:24 AM
  • The relationID doesn't look complete, I tried to use the following code to group it together: Hopefully, it can get you started:

    Create table #t(Id int, DTime time, Category Varchar(50), relationId int, GroupId int)
    
    insert into #t Values(	1, '10:00', 'StartDate', 1, Null)
    insert into #t Values(	2, '11:00', 'EndDate', 1, Null)
    insert into #t Values(	3, '12:00', 'StartDate', 3, Null)
    insert into #t Values(	4, '13:00', 'EndDate', 3, Null)
    insert into #t Values(	5,'13:10', 'StartDate',5, Null)
    insert into #t Values(	6, '13:20', 'EndDate', 5, Null)
    insert into #t Values(	7, '16:00', 'StartDate',7, Null)
    insert into #t Values(	8, '17:00', 'EndDate',8, Null)
    
    select t1.id,t1.Category, t1.DTime,t2.category, t2.DTime from 
    (Select * from #t where Category = 'StartDate') t1
    full join (Select * from #t where Category = 'EndDate')  t2 on t1.Id = t2.RelationId


    -- Please mark my post as an answer if I helped you to resolve the issue or vote up if it helped, thank you--

    Monday, May 20, 2013 6:25 AM
  • Sorry for my mistake:

    The Last record's RelationId is 7 not 8

    Id, Time, Category, relationId, GroupId,

    1, 2012-1-1 10:00, StartDate, 1, Null

    2, 2012-1-1 11:00, EndDate, 1, Null

    3, 2012-1-1 12:00, StartDate, 3, Null

    4, 2012-1-1 13:00, EndDate, 3, Null

    5,2012-1-1 13:10, StartDate,5, Null

    6, 2012-1-1 13:20, EndDate, 5, Null

    7,2012-1-1  16:00, StartDate,7, Null

    8, 2012-1-1 17:00, EndDate,7, Null

    Monday, May 20, 2013 6:32 AM
  • Are you looking for the below: Note: If you have only 2 groups, it will work. But for multiple group, you need to work in different way. Please clarify.

    Create table #t(Id int, DTime Datetime, Category Varchar(50), relationId int, GroupId int)
    insert into #t Values(	1, '2012-1-1 10:00', 'StartDate', 1, Null)
    insert into #t Values(	2, '2012-1-1 11:00', 'EndDate', 1, Null)
    insert into #t Values(	3, '2012-1-1 12:00', 'StartDate', 3, Null)
    insert into #t Values(	4, '2012-1-1 13:00', 'EndDate', 3, Null)
    insert into #t Values(	5, '2012-1-1 13:10', 'StartDate',5, Null)
    insert into #t Values(	6, '2012-1-1 13:20', 'EndDate', 5, Null)
    insert into #t Values(	7, '2012-1-1 16:00', 'StartDate',7, Null)
    insert into #t Values(	8, '2012-1-1 17:00', 'EndDate',7, Null)
    ;With cte
    As
    ( 
    	Select A.Id 'I1',B.Id 'I2',A.DTime 'D1', B.DTime 'D2', A.Category 'C1',B.Category 'C2',
    	A.relationId 'R1',B.relationId 'R2',A.GroupId 'G1',B.GroupId 'G2' 
    	From #t A
    	Inner Join #t B on B.Id = A.Id+1
    )
    update A Set GroupId  =  Case When ID <(
    Select Max(i1) From cte Where DATEDIFF(MINUTE,D2,D1) <160
    ) Then  1 
    Else 2
    End
    From #t A
    Select * From #t
    Drop table #t


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, May 20, 2013 7:27 AM
  • Why do you need to have a 160min check along with RelationId? RelationId key seems sufficient for your needs. You could you consider RelationId as a self-referencing FK ensuring no orphan records.


    -- Please mark my post as an answer if I helped you to resolve the issue or vote up if it helped, thank you--

    Monday, May 20, 2013 11:04 AM