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 |
|
Conjurer
Starting Member
30 Posts |
 Posted - 2005-11-04 : 18:10:38
|
I have a billing database with patient names in it. I received a tab delimited file from insurance plan of our roster of assigned patients. I now want to compare the insurance roster to our database to see who is missing.The roster is layed out like this (info jumbled to protect privacy):Eligibility List Sample Last Name First Name Date of Birth Gender Insured ID VW Acct #ALLEN CARRIE A 4/16/1939 F DH36664A 572576-02BAKER AMBER S 11/24/1956 F FXI2824C 596439-02BARKLOW LOREN R 12/15/1956 M KVF0092A 588878-01BRENNAN PATRICIA A 1/14/1959 F FXI8763A 549675-02BROWN MARTHA E 8/14/1967 F BD65508A 366963-02CALDWELL MICHAEL V 12/19/1969 M LR500N2J 595087-01CLARK CYNTHIA A 4/24/1971 F VO600M8O 596011-02DEMPSTER SCOTT A 2/21/1976 M CC85242A 573371-01DUNNE ANNETTE M 10/26/1976 F AE88375D 598423-02DUNNE CHRISTOPHER M 8/1/2021 M BV81536A 598423-01 I have loaded the text into an Excel Spreadsheet to work with it.I was able to query our patient profile data base to get people with this insurance plan...but of course the data is never an ideal match.For instance, some of the roster patients above have Middle Initials Concatenated to the First Name. In my database it is a mixed bag of missing initials, initials concatenated to first name or initials in separate Middle field. Thus a strict match on name is not going to work.Date of Birth should hopefully be valid between both data sources.Probably the best source of data to validate on would be the VW Acct# as I trust this to be the same in both sets of data. However in my patient data base it is buried in a note field preceded by a "Vital Works ID: " and then the number 602659-02. Generally it is the first part of the note field, but there could be additional notes preceding or trailing this Vital Works ID info.An example of the query I was able to pull from the patient data base is as follows: Last First Middle Date of Birth Gender NotesClark Lawrence J 9/7/1955 M Vital Works ID: 7575-01Clark Kayleeann NULL 1/3/1955 F Vital Works ID: 7575-02Cole Cody NULL 8/19/1948 F Vital Works ID: 8771-02 snt ref req to ohms for impact appt tbs Sent ref req back to ohms for Impact-DX.Creasey Wade L 7/9/1988 F Vital Works ID: 602659-02Kenny Roy J 2/27/1953 F Vital Works ID: 602679-02Utt Jannie C 4/11/1984 M Vital Works ID: 602715-01West Alicia G 9/9/1992 M Vital Works ID: 602736-02Wright Minnie O 2/17/1991 M Vital Works ID: 602736-03Yankee Donald E 10/27/1996 M Vital Works ID: 602762-03Yankee Stephana A 4/4/2001 F Vital Works ID: 602762-04 How could I now construct a query that would tell me what patients were in the eligibility roster that didnt have a match in the patient database?I would like to then save that to Excel or somewhere that I could print it out from so I can have someone up date the database.Thanks for your help. |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-11-05 : 14:02:34
|
Hi,Lets do this job step by step. Step 1 :-As the data which you recieved is in tab deliminated, its very important that we move this to the database, so that we can compare. This can be done by using DTS. Step 2 :-Once the file is imported to the database, its now time to write sql query to compare. Let's assume that the table to which you have imported the data is named as TAB_FILE.Select * from TAB_FILE T, PATIENT_DETAILS Pwhere 'Vital Works ID: '+T.VW_Acct = left(P.Notes,25)Let me explain, what I have done above.TAB_FILE with Alias T is the table where we have exported the tabed deliminated file.PATIENT_DETAILS with Alias P is the table in patient database.quote:
However in my patient data base it is buried in a note field preceded by a "Vital Works ID: " and then the number 602659-02.
As in the note field it is already concatinated to the value, I have explicitly added 'Vital Works ID: ' to compare.quote:
Generally it is the first part of the note field, but there could be additional notes preceding or trailing this Vital Works ID info."
As I can see from the sample data posted by you, the addition notes are added to the value at the end of some of reords, I am extracting first 25 characters to compare the value.RegardsSachin SamuelDon't sit back because of failure. It will come back to check if you still available. -- Binu |
 |
|
|
Conjurer
Starting Member
30 Posts |
Posted - 2005-11-06 : 01:37:41
|
That makes sense sort of, but couple questions.1. you said to import the tab delimted data to a table. I am not sure how to do this...you said:quote:
This can be done by using DTS.
What is DTS?2. You suggested the following Select. quote:
Select *from TAB_FILE T, PATIENT_DETAILS Pwhere 'Vital Works ID: '+T.VW_Acct = left(P.Notes,25)
Wouldn't that return the data that has a match in Patient_Details? What I am actually after is a list of rows from Tab_File where there is no match in Patient_Details.3. I like the notes idea Where clause. I think I am going to have to zero in on the position of the "-" in the Acct # as the actual length of the account number varies. The only certainty is that if follows the ":" and it ends with a "-" followed by two digits.Your ideas are helping me Zero in here. Thanks for taking the time to post ideas.Anyone have any ideas on these additional thoughts? |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-11-06 : 02:46:36
|
| 1. What is DTS?DTS is Data Transformation Services. You can check Books Online(BOL) for more information. Also check the url below.http://www.databasejournal.com/features/mssql/article.php/10894_3325731_12. I am sorry as I misunderstood. I am correcting the query below.Select *from TAB_FILE where 'Vital Works ID: '+T.VW_Acct not in ( select left(Notes,25) from PATIENT_DETAILS) This will return all the records which are there in file but not in database.3. If all the values in Acct # has got database are prefixed with 'Vital Works ID: ' then you can also use charindex and substring function to extract and compare them. Please check BOL for more details.RegardsSachin SamuelDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
Conjurer
Starting Member
30 Posts |
Posted - 2005-11-07 : 11:42:45
|
| Thanks, that is very helpful. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-04-08 : 09:34:09
|
| A 5 year old post (with a resolution) and you now list a website which has little known history. Is something afoot? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 09:40:40
|
| Spam. Another product added to my "avoid" list ... |
|
|
|
|
|
|
|
|
Need to compare data for what is missing...