I have a patient database I am trying to INSERT or UPDATE data, with values parsed from an email, depending on whether the patient already exists in the database.
To parse the email I am using a program call Email2DB. The program will parse and collect values from the email, and does a SQL query INSERT or UPDATE depending on SELECT. It allows custom query scripts. (
www.email2db.com)
Table Column Design (Table name is Patient)
[Chart Number] - Key Field
[Last Name]
[First Name]
[Social Number]
[Birth Date]
[Sex]
[Anything]
Email2DB Variables and example values
@Chart_Number 9999010190 (created from last 4SSN+DOB)
@Last_Name Doe
@First_Name John
@ Socail_Number 999-99-9999
@Birth_Date 01/01/1990
@Sex 0 (0 for male and 1for female)
@Anything = anything
Matching Algorithm Needed
I need to check whether the patient already exists in the database. Here is the algorithm I need.
1. Read patient registration information received by email and gather data fields. (Done by Email2DB)
2. Determine if record is new or existing (also need to be done in Email2DB)
IF [Chart Number] == @Chart_Number
IF ([Last Name] = @Last_Name AND [Birth Date] = @Birth_Date)
UPDATE record.
ELSE IF [Social Number] == @Social_Number
IF ([Birth Date] == @Birth_Date AND [Last Name] == @Last_Name AND [First Name] == @First_Name)
UPDATE record
ELSE IF [Last Name] == @Last_Name
IF ([First Name] == @First_Name AND [Brith Date] == @Birth_Date AND [Sex] == @Sex)
UPDATE record
ELSE
INSERT record
Current Query commands (generate by Email2DB and modified by me)
Insert Query
INSERT INTO Patient ([Chart Number], [Last Name], [First Name], [Social Number], [Birth Date], [Sex], [Anything]) VALUES (@Chart_Number, @Last_Name, @First_Name, @Social_Number, @Birth_Date, @Sex, @Anything)
Update Query
UPDATE Patient SET [Last Name] = @Last_Name, [First Name] = @First_Name, [Social Number] = @Social_Number, [Birth Date] = @Birth_Date, [Sex] = @Sex, [Anything] = @Anything
Notes: Chart Number is omitted because it should not be change for existing patient.
Select Query (I will list two queries I tried out since I dont know how to combine these conditions)
Query 1: SELECT * FROM Patient WHERE ([Chart Number] = @Chart_Numer AND [Last Name] = @Last_Name AND [Birth Date] = @Birth_Date)
Query 2: SELECT * FROM Patient WHERE ([Last Name] = @Last_Name AND [Social Numer] = @Social_Number AND [Birth Date] = @Birth_Date AND [Last Name] = @Last_Name AND [First Name] = @First_Name)
Problem
1. Lets say I have a patient in my table with following record: [Chart Number] = 9999010190, [Last Name] = Doe, [First Name] = John, [Social Number]=999-99-9999, [Birth Date] = 01/01/1990, [Sex] = 0
2. Query 1 works just fine if I have the correct Chart Number, Last Name, and Birth Date. It will find the correct record and UPDATE it.
3. If I have incorrect Chart Number, and has to use query 2, Email2DB will report existing record update but if I look at the actual table nothing is updated.
4. I think this is because I dont have the chart number, which is the key field, SQL cannot find the record. Am I right?
5. So I think what need to be done is
- if the chart number is incorrect, but it is still a existing patient (using the matching algoritham)
- Extract the correct chart number from the Patient table and put it in @Chart_Number
- Then do an UPDATE.
Any help will be greatly appreciated.
Start Free Trial
