Adress interval into multiple records
-
Hi There
I have a table with adresses like this,
ID, roadname, house_number_start, house_number_end
345, Appelroad, 1, 3
897, Pieroad, 6, 14
What I need is a table with adresses like this,
ID, roadname, house_number
345, Appelroad, 1
345, Appelroad, 3
879, Pieroad, 6
879, Pieroad, 8
879, Pieroad, 10
879, Pieroad, 12
879, Pieroad, 14
Any form of advice is very much welcome.
Regrads Joejoe
-
create your new table (I'm assuming you know how to do that).
insert into tNewTableName
(ID, roadname, house_number)
select ID, roadname, house_number_start
union
select ID, roadname, house_number_end
If the phone doesn't ring...It's me.
-
Thanks for your reply Charles
But this will only give the house_number_start and house_number_end, and if there is more than two housenumbers in the interval (like Pieroad 6, 14) I will be missing 8, 10 and 12
I'm totally blank, no idea how to do it.
Hope that you have another suggestion
Best regards Joejoe
-
Are the values for the intervals within the database or are they assumed values?
If they are assumed values, is the interval always the same?
If they values are somewhere in the database can you give a sample of one that has more than 3 houses and what that record(s) would look like?
If the phone doesn't ring...It's me.
-
The values are within the database. Here is a screendump of the twelve largest intervals
Regards Joejoe
-
no love on the screen dump
Try cutting and pasting the output of query analyzer
If the phone doesn't ring...It's me.
-
Ok here is a small sample (there is 2600 records in the original table)
"ID";"House_number_start";"House_number_end";"interval_len"
16294;601;625;24
16295;601;625;24
16296;601;625;24
16297;601;625;24
16298;601;625;24
16299;601;625;24
16300;601;625;24
16301;601;625;24
16303;601;625;24
16305;601;625;24
16306;601;625;24
16307;601;625;24
16308;601;625;24
32799;3;27;24
22394;3;27;24
22474;3;27;24
6345;109;134;25
31244;19;45;26
3837;1;27;26
3838;1;27;26
3839;1;27;26
31415;25;51;26
27577;1;27;26
28184;1;28;27
22369;40;68;28
31341;1;29;28
22367;32;60;28
3277;46;74;28
3278;46;74;28
3279;46;74;28
17705;25;53;28
22376;1;30;29
20180;31;61;30
31578;1;31;30
31246;1;31;30
22162;65;95;30
24521;2;32;30
22014;3;33;30
16918;114;144;30
16909;114;144;30
16910;114;144;30
16911;114;144;30
16912;114;144;30
16913;114;144;30
16914;114;144;30
16915;114;144;30
16916;114;144;30
16917;114;144;30
16919;114;144;30
16903;114;144;30
16904;114;144;30
16905;114;144;30
16906;114;144;30
16907;114;144;30
16908;114;144;30
22326;9;39;30
22370;1;31;30
19515;170;200;30
25170;7;39;32
31343;2;34;32
33199;2;36;34
29699;1;36;35
31738;1;37;36
21790;198;234;36
19654;236;272;36
21877;2;40;38
21976;1;39;38
20239;2;42;40
25959;2;42;40
22377;5;45;40
24176;21;63;42
32364;58;100;42
32365;58;100;42
32490;58;100;42
31825;28;74;46
25681;17;63;46
31219;10;58;48
25684;1;49;48
19509;5;53;48
23876;2;52;50
32973;12;62;50
35009;1;51;50
30221;6;56;50
31914;1;53;52
32774;22;76;54
32775;22;76;54
32758;22;76;54
20041;2;62;60
34255;1;71;70
24810;7;77;70
31800;101;175;74
34476;1;76;75
34477;1;76;75
22939;8;84;76
25167;148;224;76
21875;58;134;76
11594;262;346;84
11595;90;174;84
11596;2;86;84
11597;348;432;84
11598;176;260;84
11599;434;520;86
3158;7;93;86
3159;7;93;86
3161;7;93;86
-
OK. I believe this will do the trick. Not having the tables I really didn't have a way to debug the code, so you may need to tweek it a little bit. Hope this works.
declare @ID int,
@RoadName varchar(30), --whatever the columntype of the roadname is
@HouseStart int,
@HouseEnd int,
@Interval int,
@CurrentHouse int
declare curHouses cursor static for
select ID, roadname, house_number_start, house_number_end, interval
from curHouses
fetch next from curhouses into @ID, @RoadName, @HouseStart, @HouseEnd, @Interval
while @@fetch_status <> -1
begin
set @CurrentHouse = @HouseStart
While @CurrentHouse <= @HouseEnd
begin
insert into tNewTableName
(ID, roadname, house_number)
values
(@ID, @roadname, @CurrentHouse)
set @CurrentHouse = @CurrentHouse + @Interval
end
fetch next from curhouses into @ID, @RoadName, @HouseStart, @HouseEnd, @Interval
end
close curHouses
deallocate curHouses
If the phone doesn't ring...It's me.
-
Thanks alot Charles.
I'm going on chrismas vacation today, but I will take a look at it when I'm back in next week.
Merry chrismas
-
Assuming you have a "Numbers" table with values (0,1,2,.......)
You can do it like this:
insert into tNewTableName (ID, roadname, house_number)
select ID, roadname, house_number_start + n.Numb
from curHouses h join Numbers n on n.Numb <= h.interval
HTH
* Noel -
Hi friends
I'm back from Chrismas and have been doing alot of other suff.
Ok, I took Charles script
tweeked it a bit (open cursor was missing)And it works like a dream.
_________________________________________________________________________
Drop table tNewTableName
Create table tNewTableName (
ID int,
roadname Varchar (30),
house_number int
declare @ID int,
@RoadName varchar(30),
@HouseStart int,
@HouseEnd int,
@Interval int,
@CurrentHouse int
declare curHouses cursor static for
select ID, roadname, house_number_start, house_number_end, interval
from curHouses
fetch next from curhouses into @ID, @RoadName, @HouseStart, @HouseEnd, @Interval
while @@fetch_status <> -1
begin
set @CurrentHouse = @HouseStart
While @CurrentHouse <= @HouseEnd
begin
insert into tNewTableName
(ID, roadname, house_number)
values
(@ID, @roadname, @CurrentHouse)
set @CurrentHouse = @CurrentHouse + @Interval
end
fetch next from curhouses into @ID, @RoadName, @HouseStart, @HouseEnd, @Interval
end
close curHouses
deallocate curHouses
___________________________________________________________________________
But now I realize that the colon interval (Interval_len in the original table) is wrong!
I calculated the interval by saying (House_end - House_Start), but where I come from we have equal house numbers on one side of the road and unequal house numbers on the other.
So it works for 345, Appelroad, 1, 3 (3 - 1 = 2) and Charles script says
set @CurrentHouse = @CurrentHouse + @Interval
But it doesn't work for 897, Pieroad, 6, 14
Anyone have an idea on how to calculate the interval?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply