December 11, 2012 at 3:49 am
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
December 11, 2012 at 3:54 am
select *
,GroupNumber = (
case
when HouseNumber <= 214
then 1
when HouseNumber <= 238
then 2
else 3
end
)
from @Tmp t
order by t.Housenumber
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 11, 2012 at 4:32 am
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.
December 11, 2012 at 4:45 am
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.
December 11, 2012 at 4:48 am
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
December 11, 2012 at 4:51 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 11, 2012 at 4:56 am
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 😎
December 11, 2012 at 4:57 am
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 🙂
December 11, 2012 at 5:25 am
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🙂 🙂 🙂
December 11, 2012 at 5:35 am
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.
December 11, 2012 at 6:04 am
I'm glad to help. You are welcome!
December 11, 2012 at 6:05 am
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"...
December 11, 2012 at 6:30 am
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'.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply