Newbie question

Question

Post reply

Newbie question

Derric Raggs

Newbie

Points: 3
More actions
March 2, 2005 at 2:58 pm

#88144

I have a need to define what a duplicate is meaning if a records has the same account number then account 1 status is pending and account 2 status is billed then that is a duplicate, but if account 1 status is pending and account 2 status is hold this is not a duplicate. what is the best way to do this?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michèle Sablon Newbie Points: 3 More actions · Answer 1

SELECT accountID, count(*) FROM Accounts WHERE status not in ('hold') GROUP BY accountID HAVING COUNT(*) > 1

will return you the list of accounts being duplicated

Derric Raggs Newbie Points: 3 More actions · Answer 2

Thanks for your reply Michele, but that would not cover the conditions. I have nine all together. I only put in an example. If account 1 status is hold and account two status is hold that is a duplicate. The end result should be all standard duplicates plus the defined ones.

thanks

Wayne West SSC-Insane Points: 22586 More actions · Answer 3

Off the top of my head....

select 'Duplicate', acctid, Status

from accounts

where status = 'hold'

and acctid in (

select acctid

from accounts

where status = 'billed')

Maybe something along these lines. With your nine conditions, it's going to get complicated. You might consider doing each condition as a view then using a union query to join the views together.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Derric Raggs Newbie Points: 3 More actions · Answer 4

Thanks for your reply. what I have started to do is create a fuction with the differnt conditions

Thanks