Solved

SQL Syntax

Posted on 2009-07-01
18
243 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:healthcheckinc
  • 6
  • 5
  • 5
  • +1
18 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
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
 

Author Comment

by:healthcheckinc
Comment Utility
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
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
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
 

Author Comment

by:healthcheckinc
Comment Utility
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
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
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
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
@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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:healthcheckinc
Comment Utility
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
 

Author Comment

by:healthcheckinc
Comment Utility
One more thing, they are not exact duplicates. 1 has a leading zero and the other does not...
0
 
LVL 10

Expert Comment

by:dwe761
Comment Utility
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
 
LVL 10

Accepted Solution

by:
dwe761 earned 250 total points
Comment Utility
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
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
@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
 

Author Comment

by:healthcheckinc
Comment Utility
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
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:healthcheckinc
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now