SQL Syntax

I already asked this question but I wanto ask it again but in a different way. I have duplicate values(not really but..). The duplicate would be when a pataccount(which is not a key of any sort, its a varchar(10)) and one of the Pataccounts have a leading zero and the other one doesnt. They both have unique AcctIDs(Primary Key). The question is how do I get a row back with both the Pat Account with the leading zero and the PatAccount without the leading 0 in the same row. It also has to have the status of both. I want it to look as follows...

AcctID      PatAccount   Status    PatAccount   Status
123             56789           CW           056789          UP
456             89768           PO            089768          NC
healthcheckincAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hope this helps:

SELECT CASE WHEN isnumeric(PatAccount) = 1 THEN cast(PatAccount AS int)
ELSE cast(isnull(rtrim(PatAccount),0) AS int) AS PatAccount_without_zero,
CASE WHEN isnumeric(PatAccount) = 1 THEN '0' + cast(PatAccount AS varchar(10))
ELSE '0' + cast(isnull(rtrim(PatAccount),0) AS int) AS PatAccount_with_zero
0
dwe761Software EngineerCommented:
I'm not quite sure of your table structures but give this a try.  It wasn't clear if 56789 and 056789 are on different rows in the table?
Also, do both of these PatAccounts have different AcctID's but you are only showing the first one?  Or can AcctID be used to join them together.  See if this gives you an idea of what you need to do.
SELECT MyTable.AcctID , MyTable.PatAccount, MyTable.Status, MyTable_2.sPatAccount, MyTable_2.Status
FROM MyTable
LEFT OUTER JOIN
(SELECT PatAccount AS sPatAccount, convert(int,PatAccount) as iPatAccount, Status from MyTable WHERE Left(PatAccount,1) = '0') as MyTable_2
On MyTable.PatAccount = MyTable_2.iPatAccount

Open in new window

0
healthcheckincAuthor Commented:
Actually there are Pataccounts that the same numbers but one has a leading zero. They both have there own record. They both already exist. I needed to update my recordset because they both have different AcctIDs. Am I making sense?

AcctID      PatAccount   Status      AcctID       PatAccount   Status
123             56789           CW           789          056789          UP
456             89768           PO            987          089768          NC
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

dwe761Software EngineerCommented:
Then change my query as follows:
(Please note this is untested)
SELECT MyTable.AcctID , MyTable.PatAccount, MyTable.Status, MyTable_2.sAcctID, MyTable_2.sPatAccount, MyTable_2.sStatus
FROM MyTable
LEFT OUTER JOIN
(SELECT MyTable_2.AcctID AS sAcctID, PatAccount AS sPatAccount, convert(int,PatAccount) as iPatAccount, Status As sStatus from MyTable WHERE Left(PatAccount,1) = '0') as MyTable_2
On MyTable.PatAccount = MyTable_2.iPatAccount

Open in new window

0
healthcheckincAuthor Commented:
Im sorry dwe, Im trying to convert everything to my table name and it doesnt seem to be working. The table is accounts_master....Could you give me a hand again...Thanks so much
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Have you tried using my query, Including the table names here and if your prior result set query was:

SELECT AcctID, PatAccount, Status, AcctID, PatAccount, Status
FROM accounts_master

Then your query would look like:

Hope this helps
SELECT AcctID, CASE WHEN isnumeric(PatAccount) = 1 THEN '0' + cast(PatAccount AS varchar(10))
ELSE '0' + cast(isnull(rtrim(PatAccount),0) AS int) AS PatAccount_with_zero, Status as Status1, AcctID, CASE WHEN isnumeric(PatAccount) = 1 THEN cast(PatAccount AS int)
ELSE cast(isnull(rtrim(PatAccount),0) AS int) AS PatAccount_without_zero, Status as Status2
FROM accounts_master

Open in new window

0
8080_DiverCommented:
This will probably do it for you.
SELECT T1.AcctID , 
       T1.PatAccount, 
       T1.Status, 
       T2.sAcctID, 
       T2.sPatAccount, 
       T2.sStatus
FROM accounts_master AS T1
LEFT OUTER JOIN
(SELECT AcctID AS sAcctID, 
        PatAccount AS sPatAccount, 
        CONVERT( VarChar(10), convert(int,PatAccount)) AS iPatAccount, 
        Status As sStatus 
 FROM   accounts_master 
 WHERE Left(PatAccount,1) = '0'
) AS T2
ON T1.PatAccount = T2.iPatAccount
WHERE T2.iPatAccount IS NOT NULL;

Open in new window

0
dwe761Software EngineerCommented:
Try something like this.  By the way, what error are you getting?

Re: 8080_Diver's suggestion, I don't think you want the last WHERE clause because I'm assuming you want to pull in rows from T1 even when there is not a corresponding PatAccount with leading 0.

SELECT AM.AcctID , AM.PatAccount, AM.Status, 
			AM_2.sAcctID, AM_2.sPatAccount, AM_2.sStatus
FROM accounts_master AM
LEFT OUTER JOIN
(SELECT 
	AcctID AS sAcctID, 
	PatAccount AS sPatAccount, 
	convert(int,PatAccount) as iPatAccount, 
	Status As sStatus 
from accounts_master WHERE Left(PatAccount,1) = '0') as AM_2
On AM.PatAccount = AM_2.iPatAccount

Open in new window

0
8080_DiverCommented:
@dwe761,
Actually, the initial question seems to imply a desire to see only the "duplicate" rows.
Aslo, the ON clause in your query is forcing an implicit conversion of either the PatAccount or the iPatAccount, which may well result in performance degradation.  
@healthcheckinc,
I guess you have to be the final arbiter of the question as to whether you want only the "duplicates" (i.e. include the final WHERE clause) or not (i.e. omit the final WHERE clause). ;-)
0
healthcheckincAuthor Commented:
All,

I see that there are alot of Converts going on and its possible from my one post you thought thats what I wanted to do. Simplified....I have 2(well alot more than 2 but...) records in the same table that I need to identify. One will look like this 0123456789 and the other will look like this 123456789 in seperate records. I want to bring in the acctID, patient_account and the status for each of them. No converting, no concatenating just a visual of the 2 rows values into 1 row....I also have to have Where hospID = 1...Does this make sense? Sorry for any confusion

acctID    PatAccount     Status    acctID         PatAccount      Status
123       0123456789      CW         987            123456789        UP
0
healthcheckincAuthor Commented:
One more thing, they are not exact duplicates. 1 has a leading zero and the other does not...
0
dwe761Software EngineerCommented:
Yes, we've already given you a queries to accomplish what you're after.  Perhaps what is unclear to you is that the convert is necessary because we are changing a string to numeric in order to be able to join the table to the subquery together on a field of a common type. So, 8080_Diver's point is that to get better performance, you should convert the field to join so the server does not have to figure that out.

The question we posed to you is do you ONLY want to return rows where there are both 123456789 and 0123456789 pairs or do you want to return rows sometimes when there is not both pairs but only one of them.  If you ONLY want to return rows that have both with and without a leading zero, then include the WHERE clause:
WHERE AM_2.iPatAccount IS NOT NULL;
SELECT AM.AcctID , AM.PatAccount, AM.Status, 
                        AM_2.sAcctID, AM_2.sPatAccount, AM_2.sStatus
FROM accounts_master AM
LEFT OUTER JOIN
(SELECT 
        AcctID AS sAcctID, 
        PatAccount AS sPatAccount, 
        convert(varchar(10),convert(int,PatAccount)) as iPatAccount, 
        Status As sStatus 
from accounts_master WHERE Left(PatAccount,1) = '0') as AM_2
On AM.PatAccount = AM_2.iPatAccount

Open in new window

0
dwe761Software EngineerCommented:
Example output if NOT using the last WHERE clause:

acctID    PatAccount     Status    acctID         PatAccount      Status
123       0123456789      CW         987            123456789        UP
444       0777777777      XX
555       055555555        PP           888            55555555         UU
666       998877668        QQ      

Example output WITH the WHERE clause:

acctID    PatAccount     Status    acctID         PatAccount      Status
123       0123456789      CW         987            123456789        UP
555       055555555          PP          888           55555555       UU
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
8080_DiverCommented:
@healthcheckinc,
Any actions done in a SELECT statement do not have any impact on any data in the database.  THose actions only impact what is going on in the SELECT statement.
In other words, the convert statements are not in any way modifying any data in the database but, rather, are only modifying how the data may appear in the results of the SELECT statement (assuming that they modified values appear in the final set of selected columns).
Please, note, I am trying very hard not to raise questions about the fact that the PatAccount is a character column (which has contributed to this problem) and is apparently being allowed to be entered by either more than one person or more than one process and without any consistent controls on what is being entered.
0
healthcheckincAuthor Commented:
8080 Diver: I understand the concept of a Select, Im trying to just let everyone know that no Convert or concat is neccesary. I did not build the database, although I did build a program that filters out any possible duplicate so going forward this wont happen again. I am in the process of cleaning up old data.
This worked to build a table of dups

select am.account_id,am.patient_account as 'Dup',am.audit_status as 'DupStatus',
am1.GAccountID,am1.GPatAccount,am1.PatAcctIDStatus
into DupsTest
from accounts_master am
inner join
(select account_id as 'GAccountID',patient_account as 'GPatAccount',
right(patient_account,9)as 'PatAcctID',audit_status as 'PatAcctIDStatus'
from accounts_master where len(patient_account)>9 and Hospital_id = 1
0
8080_DiverCommented:
healthcheckinc,
Im trying to just let everyone know that no Convert or concat is neccesary.
 No offense taken by your statement and no offense intended by the following.
How is it that you know that no convert is required given that you have asked for our help?  Are you now stating a new constraint on the nature of the solution you are asking for?  
What I am saying is that the CONVERTS that I used in the query I provided are for expediency and do not have anything to do with what is displayed (other than making it possible to select the desired results).  If the solution provided works, whether it has concatenate, convert, cast, or even reverse (and, yes, I can think of a solution that uses REVERSE ;-), does anything matter except the fact that it works and it does so with reasonable speed? ;-)
Now, if you must have tis solution sans converts and casts, then are you willing to allow the use of LEFT and RIGHT functions? ;-)
0
8080_DiverCommented:
Perhaps we should have first asked whether the PatAccount values without leading zeros all have the same number of characters and whether the PatAccount values with leading zeros all have a length of 1 more than those without.
That would change the approach somewhat . . . although the resulting query would simply ba another way of getting to the same results.
 
0
healthcheckincAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.