Post reply

Difficult Grouping problem

  • May 17, 2017 at 3:11 am

    #321421

    Hello there,

    I have the following derived table extract below :-

    RowId Date Value NextValue InSequence
    205 20/09/2016 1128491 1128492 1
    206 20/09/2016 1128492 1128493 1
    207 20/09/2016 1128493 1128494 1
    208 20/09/2016 1128494 1128495 1
    209 20/09/2016 1128495 1128496 1
    210 20/09/2016 1128496 1128727 0
    211 21/09/2016 1128727 1128728 1
    212 21/09/2016 1128728 1128729 1
    213 21/09/2016 1128729 1128730 1
    214 21/09/2016 1128730 1128731 1
    215 21/09/2016 1128731 1128732 1

    I need to introduce a Grouping column so I can group the relevant data together in subsequent calculations, like the below.

    RowId Date Value NextValue InSequence Grouping
    205 20/09/2016 1128491 1128492 1 1
    206 20/09/2016 1128492 1128493 1 1
    207 20/09/2016 1128493 1128494 1 1
    208 20/09/2016 1128494 1128495 1 1
    209 20/09/2016 1128495 1128496 1 1
    210 20/09/2016 1128496 1128727 0 1
    211 21/09/2016 1128727 1128728 1 2
    212 21/09/2016 1128728 1128729 1 2
    213 21/09/2016 1128729 1128730 1 2
    214 21/09/2016 1128730 1128731 1 2
    215 21/09/2016 1128731 1128732 1 2

    But I'm struggling. I'm guessing I'll need to use a recursive CTE here. Any suggestions welcome.

  • May 17, 2017 at 3:18 am

    #1942830

    Looks like the grouping is on the date, dense rank should do the trick
    😎

  • May 17, 2017 at 3:20 am

    #1942832

    Date may come in useful but you can have several groups within the same date unfortunately.

  • May 17, 2017 at 3:21 am

    #1942833

    To save others time:
    CREATE TABLE #Sample
      (RowID int,
      [Date] date,
      [Value] int,
      NextValue int,
      InSequence int);
    GO
    INSERT INTO #Sample
    VALUES
      (205,'20160920',1128491,1128492,1),
      (206,'20160920',1128492,1128493,1),
      (207,'20160920',1128493,1128494,1),
      (208,'20160920',1128494,1128495,1),
      (209,'20160920',1128495,1128496,1),
      (210,'20160920',1128496,1128727,0),
      (211,'20160921',1128727,1128728,1),
      (212,'20160921',1128728,1128729,1),
      (213,'20160921',1128729,1128730,1),
      (214,'20160921',1128730,1128731,1),
      (215,'20160921',1128731,1128732,1);
    GO

    DROP TABLE #Sample;
    GO

    Please ensure you provide your sample data with DDL and INSERT statements 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 17, 2017 at 3:22 am

    #1942834

    Paul Treston - Wednesday, May 17, 2017 3:20 AM

    Date may come in useful but you can have several groups within the same date unfortunately.

    So how do you define a group?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 17, 2017 at 3:56 am

    #1942838

    Thanks for the tip with formatting query etc.
    The only thing I have to go on is the Value/NextValue and whether it's InSequence or not, which I've already derived in my query.

  • May 17, 2017 at 4:08 am

    #1942840

    In the sample data you provided, there's a group for each distinct date and each distinct date is in its own group.  Please post some more sample data illustrating a group that contains more than one date.

    John

  • May 17, 2017 at 4:43 am

    #1942847

    Okay, in that case, I've modified the table script provided previously, so all the data appears on the same date. But there are two distinct groups to work with


    CREATE TABLE #Sample
    (RowID int,
    [Date] date,
    [Value] int,
    NextValue int,
    InSequence int);
    GO
    INSERT INTO #Sample
    VALUES
    (205,'20160920',1128491,1128492,1),
    (206,'20160920',1128492,1128493,1),
    (207,'20160920',1128493,1128494,1),
    (208,'20160920',1128494,1128495,1),
    (209,'20160920',1128495,1128496,1),
    (210,'20160920',1128496,1128727,0),
    (211,'20160920',1128727,1128728,1),
    (212,'20160920',1128728,1128729,1),
    (213,'20160920',1128729,1128730,1),
    (214,'20160920',1128730,1128731,1),
    (215,'20160920',1128731,1128732,1);
    GO

    DROP TABLE #Sample;
    GO

  • May 17, 2017 at 4:43 am

    #1942848

    Paul Treston - Wednesday, May 17, 2017 3:56 AM

    The only thing I have to go on is the Value/NextValue and whether it's InSequence or not, which I've already derived in my query.

    OK.  So this is an Islands query.  Do a search for Gaps and Islands.

     THIS POST by ChrisM is a good starting point.
    Also, you may want to take a look at THIS ARTICLE by Itzik Ben-Gan

    How to post data/code on a forum to get the best help.

  • May 17, 2017 at 6:04 am

    #1942873

    I did something similar before
    Extract RowId where InSequence=0 (ie 210)
    Include min and max IDs (ie 0 and 999)
    Sequence with rownumber
    SeqNo ID
    1 0
    2 210
    3 999
    Apply grouping with min and max
    Grouping MinID MaxID
    1 0 210
    2 211 999
    Join result to table

    Far away is close at hand in the images of elsewhere.
    Anon.

  • May 17, 2017 at 7:58 am

    #1942919

    Thanks Des. You've pointed me to a possible solution.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply