November 5, 2003 at 2:08 pm
Bill,
Here is the dup checking based on your structures:
DELETE FROM cbDoNotCall
WHERE PhoneNum IN
(SELECT PhoneNum
FROM cbDoNotCall
GROUP BY PhoneNum
HAVING COUNT(*) > 1)
AND
ID_DoNotCallRoot NOT IN
(SELECT MIN(ID_DoNotCallRoot)
FROM cbDoNotCall
GROUP BY PhoneNum)
Thats it. Essentially, you grab all the records that have duplicate BTNs except for one of each of the group of duplicated BTNs. This method is a (huge) modification of a duplicate removal piece of code that I found a couple years ago on SQLGuru.com or something like that.
Let me know if you have more questions.
-Jeff
November 5, 2003 at 2:21 pm
Thanks - will review
November 6, 2003 at 3:50 pm
quote:
In the US the numbers are 10 digits (at least for the foreseeable future) so it makes things a bit easier.
Actually I read an article a few months ago that because the US is running out of area codes, we will see longer phone numbers before too long. Don't remember how many extra digits they were talking about; my best guess is 1 or 2.
quote:
As others have said, numeric will cut off the leading zeros and I need those.
First of all--just as a kind of side note--this is no different from the bigint case.
Second of all, the leading zeros are still stored in the database--it just matters how the output is formatted.
You should make a distinction between the storage of data and its (output) formatting. The output format determines how the data fits into the destination field: whether it's left- or right-justified, zero-filled, blank filled, formatting characters inserted, or anything else.
Therefore you shouldn't disqualify the numeric / decimal format simply on this ground alone. (On the other hand, if you must do a number of character-oriented manipulations on the data before producing final output, that can affect the decision.)
November 7, 2003 at 4:49 am
quote:
quote:
In the US the numbers are 10 digits (at least for the foreseeable future) so it makes things a bit easier.Actually I read an article a few months ago that because the US is running out of area codes, we will see longer phone numbers before too long. Don't remember how many extra digits they were talking about; my best guess is 1 or 2.
Actually this is still quite a bit off as there is a lot of untapped potential for the Area Code barring 700. Also if they ever pass the bill ultimately no one will have a true line based phone and a single number which isn't tied to a geographic location, the reason for the concern is that people have 2,3 or more phone numbers for themselves. The bill is very interesting but I don't remember the name since I saw it 6 years ago when I was a long distance customer service rep for a small company. Articles to the effect that we will be adding digits have been hyped for a long time and have little merrit at this point.
November 7, 2003 at 2:43 pm
quote:
Actually this is still quite a bit off as there is a lot of untapped potential for the Area Code barring 700. Also if they ever pass the bill ultimately no one will have a true line based phone and a single number which isn't tied to a geographic location ...
Well, I don't know what you mean by "the area code barring 700," I don't know what "the bill" is, and I can't figure out the 2nd sentence.
quote:
The bill is very interesting but I don't remember the name since I saw it 6 years ago when I was a long distance customer service rep for a small company. Articles to the effect that we will be adding digits have been hyped for a long time and have little merrit at this point.
I googled around a bit, and found that the worst problem seems to be the fact that every phone company needs a 10,000-number block for every district served. We're running out of those blocks, and lots of switching equipment must be replaced to allow, say, 1,000-number blocks to be allocated instead. Also, phone companies are enlarging their "local service" areas to conserve blocks.
Nevertheless, the most recent article I found on the web (Feb. 2003) is somewhat chilling, and quotes authoritative sources. Here are excerpts:
"'INC's analysis says that moving to the next numbering scheme needs to begin 10 years before all numbers are exhausted,' said Randy Sanders, director of regulatory and external affairs at BellSouth.
"A few years ago, number exhaust was projected to happen in 2009. With the collapse of the telecom boom, however, that date was pushed to 2031.
"'We can't get too complacent, because a resurgence of the telecom industry can lead to number exhaust as early as 2015, and that puts us right up against the 10-year time frame,' said Robert Atkinson, chairman of the North American Numbering Council.
"If Atkinson is correct, the new numbering scheme could begin as early as 2005."
The entire article is at http://www.wired.com/news/infostructure/0,1377,57571,00.html . Other articles about running out of numbers (all more recent than 6 years ago) are at http://www.thedirectory.org/pref/splits.htm and http://telephonyonline.com/ar/telecom_number_crunch_monster/ . The latter article proposes an alternative to adding digits.
November 7, 2003 at 6:57 pm
quote:
quote:
Actually this is still quite a bit off as there is a lot of untapped potential for the Area Code barring 700. Also if they ever pass the bill ultimately no one will have a true line based phone and a single number which isn't tied to a geographic location ...Well, I don't know what you mean by "the area code barring 700," I don't know what "the bill" is, and I can't figure out the 2nd sentence.
quote:
The bill is very interesting but I don't remember the name since I saw it 6 years ago when I was a long distance customer service rep for a small company. Articles to the effect that we will be adding digits have been hyped for a long time and have little merrit at this point.I googled around a bit, and found that the worst problem seems to be the fact that every phone company needs a 10,000-number block for every district served. We're running out of those blocks, and lots of switching equipment must be replaced to allow, say, 1,000-number blocks to be allocated instead. Also, phone companies are enlarging their "local service" areas to conserve blocks.
Nevertheless, the most recent article I found on the web (Feb. 2003) is somewhat chilling, and quotes authoritative sources. Here are excerpts:
"'INC's analysis says that moving to the next numbering scheme needs to begin 10 years before all numbers are exhausted,' said Randy Sanders, director of regulatory and external affairs at BellSouth.
"A few years ago, number exhaust was projected to happen in 2009. With the collapse of the telecom boom, however, that date was pushed to 2031.
"'We can't get too complacent, because a resurgence of the telecom industry can lead to number exhaust as early as 2015, and that puts us right up against the 10-year time frame,' said Robert Atkinson, chairman of the North American Numbering Council.
"If Atkinson is correct, the new numbering scheme could begin as early as 2005."
The entire article is at http://www.wired.com/news/infostructure/0,1377,57571,00.html . Other articles about running out of numbers (all more recent than 6 years ago) are at http://www.thedirectory.org/pref/splits.htm and http://telephonyonline.com/ar/telecom_number_crunch_monster/ . The latter article proposes an alternative to adding digits.
Sorry Congressional Bill (legislature on removing lan phone lines). ALso, keep in mind you will here both sides of the fence on the time frame to exhaust but the whole thing about 10,000 is not really true as numbers can be transfered between carriers (we pay a special tax for that ability which is nothing more than an assigment table in each switch to know routing, actually a bit more complicated but this is a basic shot at it).
November 10, 2003 at 12:28 pm
quote:
Sorry Congressional Bill (legislature on removing lan phone lines).
Never heard of any such bill. (I am here guessing you mean "land" phone lines, not LAN phone lines.) That kind of action sounds way premature to me--we aren't anywhere close to having cell phone service nationwide, and the quality is still spotty.
I guess if implemented this could buy some more time, but I sure don't know how much.
the whole thing about 10,000 is not really true as numbers can be transfered between carriers (we pay a special tax for that ability which is nothing more than an assigment table in each switch to know routing, actually a bit more complicated but this is a basic shot at it).
Glad to know that particular technical problem has been resolved -- must have been done after that one article was written.
November 10, 2003 at 12:34 pm
Well, for what it's worth, the bigint goes up to 9223372036854775807, which is essentially 19 places. That's an awfully big number. So, even if they change the phone numbers to 15 places, I don't have to change a thing and it's still only 8 bytes per record.
-B
December 1, 2003 at 7:08 pm
I know this is an old topic, but I've been busy. My two cents: Numbers that are not calculatable are strings. Char(10) for phone numbers. The zeros issue may not be as compelling as it is for zips and SSNs, but it's the same principle.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply