Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ntn104
Posting Yak Master
175 Posts |
 Posted - 2012-05-18 : 09:50:13
|
How do I write a query to get all transpose IDs? For example, ID1=123456789 and ID2=123456784. These two IDs actually for one person. But not sure how to write a query for that.
Thanks, |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-18 : 10:01:18
|
what do you mean by transpose ID ?
KH
[spoiler]Time is always against us[/spoiler]
|
 |
|
|
ntn104
Posting Yak Master
175 Posts |
Posted - 2012-05-18 : 10:06:56
|
I meant that they may alter the number....
for example, 123456789 and 987654321
Thanks,
quote:
Originally posted by khtan
what do you mean by transpose ID ?
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-18 : 10:09:08
|
then how would you identify that the 2 ID are same person ?
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
ntn104
Posting Yak Master
175 Posts |
Posted - 2012-05-18 : 10:20:20
|
By looking some data like: Address, Name, and some of financial figures....The first example, can be a typos ...but the second example I gave you that is deffinitely a transpose number....however, how do we get that list. If I base on same address, there may be some people live in the same house house...so that 's still not acurrate.
Thanks,
quote:
Originally posted by khtan
then how would you identify that the 2 ID are same person ?
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-18 : 10:38:56
|
quote:
Originally posted by ntn104
By looking some data like: Address, Name, and some of financial figures....The first example, can be a typos ...but the second example I gave you that is deffinitely a transpose number....
I'm sorry, but 123456789 and 987654321 are NOT transposed, they're completely reversed, and there's no way that could be a typo. I agree it's most likely bad data, but which one is correct? And how would you be certain they're not 2 legitimate IDs for 2 people living at the same address?
The first example you gave is not technically a transposition either, 123456789 vs. 123456784. A real transposition is 123456789 vs. 213456789 or 123456798. I'm not trying to split hairs, but terminology is important and what you're saying is confusing the issue. |
|
|
|
ntn104
Posting Yak Master
175 Posts |
Posted - 2012-05-18 : 11:22:52
|
I am sorry to get confuse on my first example, that maybe a typos...but I want to get the list so we can manualy exam those cases....Thinking about using Mod function....but not sure...
I agreed that no way you will know that is the real number, but like I said above, we can manually exame each accounts.
Thanks,
quote:
Originally posted by robvolk
quote:
Originally posted by ntn104
By looking some data like: Address, Name, and some of financial figures....The first example, can be a typos ...but the second example I gave you that is deffinitely a transpose number....
I'm sorry, but 123456789 and 987654321 are NOT transposed, they're completely reversed, and there's no way that could be a typo. I agree it's most likely bad data, but which one is correct? And how would you be certain they're not 2 legitimate IDs for 2 people living at the same address?
The first example you gave is not technically a transposition either, 123456789 vs. 123456784. A real transposition is 123456789 vs. 213456789 or 123456798. I'm not trying to split hairs, but terminology is important and what you're saying is confusing the issue.
|
|
|
|
|
|
|
|
|
Transpose number