Post reply

Difficult Grouping problem

  • May 17, 2017 at 3:11 am

    #321421

    Hello there,

    I have the following derived table extract below :-

    RowIdDateValueNextValueInSequence
    20520/09/2016112849111284921
    20620/09/2016112849211284931
    20720/09/2016112849311284941
    20820/09/2016112849411284951
    20920/09/2016112849511284961
    21020/09/2016112849611287270
    21121/09/2016112872711287281
    21221/09/2016112872811287291
    21321/09/2016112872911287301
    21421/09/2016112873011287311
    21521/09/2016112873111287321

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

    RowIdDateValueNextValueInSequenceGrouping
    20520/09/20161128491112849211
    20620/09/20161128492112849311
    20720/09/20161128493112849411
    20820/09/20161128494112849511
    20920/09/20161128495112849611
    21020/09/20161128496112872701
    21121/09/20161128727112872812
    21221/09/20161128728112872912
    21321/09/20161128729112873012
    21421/09/20161128730112873112
    21521/09/20161128731112873212

    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