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 987654321Thanks,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