I have a Master table which has PRFUID, incdate, incnumber, formtype and chiefcomplaint
This is loaded via SQL as a seperate table and stored into a QVD.
I have a Patient table which has PRFUID, incdate, incnumber and patientkey
This is loaded via SQL as a seperate table and stored into a QVD.
I need to have one table which has the PRFUID, incdate, incnumber, formtype and patientkey within it
Here is where I am struggling.
There are several form types, Each form is scanned in and given its own PRFUID and some of them don't capture the cheifcomplaint. A patient can have two forms, Form A with a PRFUID of 1001 and form B with an PRFUID of 2002. But both forms will have the incdate and incnumber on them.
I did join the patient table to the master table as below to get the incdate and the incprfno to build a string that would form a 'key'. I could then use this to link the patient table back to the master table and pull out the cheif complaint.
How can I join them together? This is what I have accomplished so far, but it is slow and I fear it is causing heavy CPU useage on a limited load.
TEMP_Patient:
LOAD 1 as counter_patient ,PRFUniqueId as %KEY_PRFUID ,NUM(PatientNo) as PatientNo ,EDAccessNum ,ContactNumber ,AUTONUMBER(upper(anPatFirstNames)&upper(anPatSurname)&patientDOB) as UniquePatientID ,UPPER(anPatSurname) as anPatSurname ,UPPER(anPatFirstNames) as anPatFirstNames ,UPPER(anPatGP) as anPatGP ,UPPER(ethnicGroup) as ethnicGroup ,applymap('Gender', patSex, NULL()) AS Gender ,patientDOB ,UPPER(IF(ISNULL(GPPracticeCode),anPatGPSurg,GPPracticeCode)) AS GPPracticeCode ,UPPER(patAddress) as PatientPostcode ,left(trim(patAddress),index(trim(patAddress),' ')-1) as PatientPostcodeShort //Calc Age from incDate Late ,IF(ISNULL(PatAgeYearsMonths),patAge,PatAgeYearsMonths) AS patAge ,TRIM(NHSNumber) as NHSNumber
//I have joined the Master table in SQL here to obtain the incdate and incPrfNo. ,num(IncDate)&'-'&num(IncPrfNo) as %KEY_Patient ,DATE(IncDate,'dd/mm/yyyy') AS Inc_Date ,NUM(IncPrfNo) as IncPrfNo ; SQL SELECT * FROM TCMPRF.dbo."LB03_PATIENT" pat INNER JOIN (SELECT PRFUniqueId FROM [TCMPRF].[dbo].[PE01_MASTER] where IncDate between '01-Nov-2008' and getdate() //WHERE IncDate >= DateAdd(month,-48,GETDATE()) ) prf ON pat.PRFUniqueId = prf.PRFUniqueId LEFT JOIN [TCMPRF].dbo.PE01_MASTER ma ON pat.PRFUniqueId = ma.PRFUniqueId ;
Patient: Load TEMP_Patient.counter_patient as counter_patient // ,%KEY_PRFUID ,TEMP_Patient.PatientNo as PatientNo ,TEMP_Patient.EDAccessNum as EDAccessNum ,TEMP_Patient.ContactNumber as ContactNumber ,TEMP_Patient.UniquePatientID as UniquePatientID ,TEMP_Patient.anPatSurname as anPatSurname ,TEMP_Patient.anPatFirstNames as anPatFirstNames ,TEMP_Patient.anPatGP as anPatGP ,TEMP_Patient.ethnicGroup as ethnicGroup ,TEMP_Patient.Gender as Gender ,TEMP_Patient.patientDOB as patientDOB ,TEMP_Patient.GPPracticeCode as GPPracticeCode ,TEMP_Patient.PatientPostcode as PatientPostcode ,TEMP_Patient.PatientPostcodeShort as PatientPostcodeShort ,TEMP_Patient.patAge as patAge ,TEMP_Patient.NHSNumber as NHSNumber // ,%KEY_Patient as %KEY_Patient_test // ,TEMP_Patient.Inc_Date as Inc_Date
Resident TEMP_Patient;
left join Load %KEY_Patient ,Master.ChiefComplaintOriginal as Patient.ChiefComplaintOriginal resident Master;
