Making groups using a separator line

  • Hello there,

    I have a little challenge that makes me thinking for hours now. I have a list of data and have to group them. The problem is there are records that mark the beginning of a new group which do not have other data then the rest.

    Here's the example code:

    declare @Tmp table(

    Postalcode nvarchar(10),

    Street nvarchar(100),

    Housenumber int,

    IsSeparator bit

    );

    insert into @Tmp values

    ( 12345, 'SomeStreetName', 188 , '0' )

    ,( 12345, 'SomeStreetName', 212 , '0' )

    ,( 12345, 'SomeStreetName', 214 , '0' )

    ,( 12345, 'SomeStreetName', 214 , '1' )

    ,( 12345, 'SomeStreetName', 236 , '0' )

    ,( 12345, 'SomeStreetName', 238 , '0' )

    ,( 12345, 'SomeStreetName', 238 , '1' )

    ,( 12345, 'SomeStreetName', 242 , '0' )

    ,( 12345, 'SomeStreetName', 244 , '0' )

    ,( 12345, 'SomeStreetName', 246 , '0' )

    select *,

    GroupNumber = '???'

    from @Tmp t

    order by t.Housenumber

    select *, GroupNr = 1 from @Tmp where Housenumber <=214

    union

    select *, GroupNr = 2 from @Tmp where Housenumber > 214 and HouseNumber <= 238

    union

    select *, GroupNr = 3 from @Tmp where Housenumber > 238

    order by HouseNumber

    The second query shows the result i need. Of course I want to do it not that way as the house number ranges could differ 😉

    These are house numbers of a street, ordered ascending. The column "IsSeparator" shows that there should start a new group.

    I tried a lot using row_number() or rank() but I don't get to query that get's the result shown above.

    Any help would be great.

    Thank's a lot, Wolf

  • select *

    ,GroupNumber = (

    case

    when HouseNumber <= 214

    then 1

    when HouseNumber <= 238

    then 2

    else 3

    end

    )

    from @Tmp t

    order by t.Housenumber

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • You misunderstood. I cannot use the house numbers in my query. That was just to show how the result should be. I have not only 1 street in my list there some hundred streets. And I have to group each of them by the seperator line.

  • Maybe

    ; with cte as

    (

    select *, num = (select count(*) from @Tmp t2 where t1.Postalcode = t2.Postalcode and t1.Street = t2.Street and t2.Housenumber <= t1.Housenumber and IsSeparator = 1)

    from @Tmp t1

    )

    select *, GroupNr=num+1 from cte

    where IsSeparator = 0

    order by Postalcode, Street, HouseNumber


    Cursors never.
    DTS - only when needed and never to control.

  • The problem with your posted sample is lack of details. Would be really great if you could provide better DDL. What is PK of your table? What really makes your address group?

    Also, does the row with separator is a duplicate with the same HouseNumeber?

    My sample is making assumption that you want to group by PostCode and HouseNumber.

    Please note that I've extended your sample data to include addresses with different postcodes:

    declare @Tmp table(

    Postalcode nvarchar(10),

    Street nvarchar(100),

    Housenumber int,

    IsSeparator bit

    );

    insert into @Tmp values

    ( 12345, 'SomeStreetName', 188 , '0' )

    ,( 12345, 'SomeStreetName', 212 , '0' )

    ,( 12345, 'SomeStreetName', 214 , '0' )

    ,( 12345, 'SomeStreetName', 214 , '1' )

    ,( 12345, 'SomeStreetName', 236 , '0' )

    ,( 12345, 'SomeStreetName', 238 , '0' )

    ,( 12345, 'SomeStreetName', 238 , '1' )

    ,( 12345, 'SomeStreetName', 242 , '0' )

    ,( 12345, 'SomeStreetName', 244 , '0' )

    ,( 12345, 'SomeStreetName', 246 , '0' )

    ,( 12346, 'SomeStreetName', 88 , '0' )

    ,( 12346, 'SomeStreetName', 112 , '0' )

    ,( 12346, 'SomeStreetName', 114 , '0' )

    ,( 12346, 'SomeStreetName', 114 , '1' )

    ,( 12346, 'SomeStreetName', 136 , '0' )

    ,( 12347, 'SomeStreetName', 1 , '0' )

    ,( 12347, 'SomeStreetName', 3 , '0' )

    SELECT t.*

    ,DENSE_RANK() OVER (PARTITION BY t.Postalcode

    ORDER BY ISNULL(b.Band, 9999999)-- you can use max int here

    ) as GroupId

    FROM @Tmp t

    CROSS APPLY(SELECT MIN(Housenumber) Band

    FROM @Tmp b

    WHERE b.Postalcode = t.Postalcode

    AND b.Housenumber >= t.Housenumber

    AND b.IsSeparator = 1) b

    WHERE t.IsSeparator = 0

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • WolfgangE (12/11/2012)


    You misunderstood. I cannot use the house numbers in my query. That was just to show how the result should be. I have not only 1 street in my list there some hundred streets. And I have to group each of them by the seperator line.

    Oops - sorry. I see Eugene is on the case, so no need for another dodgy post from me.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • This looks quite good. And so simple. I already tried something similar making a join between a table containing the separators and the non-separators but it did not work...

    Thank's a lot, this helps me a lot. And as I read your signature: this query is for replacing a cursor 😎

  • Phil Parkin (12/11/2012)


    WolfgangE (12/11/2012)


    You misunderstood. I cannot use the house numbers in my query. That was just to show how the result should be. I have not only 1 street in my list there some hundred streets. And I have to group each of them by the seperator line.

    Oops - sorry. I see Eugene is on the case, so no need for another dodgy post from me.

    Thank's to you too 🙂

  • Hello Eugene Elutin,

    the problem with the details is: they are no details.

    The shown table is the result of a quite complex select out of a database that I've never seen before. I just know that I get this result and approxemately what the finally result should be. And no, there is no primary key.

    BUT: very thanks, your query does EXACTLY what I need🙂 🙂 🙂

  • And to finally answer this question:

    Eugene Elutin (12/11/2012)What really makes your address group?

    The separator lines are grouping criteria. It's kind of user configuration. This means a user inserts the separator lines thus defining where he wants to have the groups. This is part of an delivery plan. The user decides how many people a street needs to deliver all parcels. Every group consists of several house numbers. So the user can define how many groups or people we need to serve the whole street and can tell the people which house number ranges they are responsible for.

  • I'm glad to help. You are welcome!

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Phil Parkin (12/11/2012)


    WolfgangE (12/11/2012)


    You misunderstood. I cannot use the house numbers in my query. That was just to show how the result should be. I have not only 1 street in my list there some hundred streets. And I have to group each of them by the seperator line.

    Oops - sorry. I see Eugene is on the case, so no need for another dodgy post from me.

    Thank you for valuing me so high!

    But I wouldn't call your posts "dodgy"...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (12/11/2012)


    Phil Parkin (12/11/2012)


    WolfgangE (12/11/2012)


    You misunderstood. I cannot use the house numbers in my query. That was just to show how the result should be. I have not only 1 street in my list there some hundred streets. And I have to group each of them by the seperator line.

    Oops - sorry. I see Eugene is on the case, so no need for another dodgy post from me.

    Thank you for valuing me so high!

    But I wouldn't call your posts "dodgy"...

    Compared with your solution, the triangular-join nonsense I was coming up with (between hitting F5 on another project!) was definitely dodgy!! 😀

    I should stick to SSIS questions - where I have recently graduated to the level of 'not too bad'.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 13 posts - 1 through 12 (of 12 total)

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