Not massively complicated, but I’m sure it’s the longest (linecount) that I’ve ever written.
Non-Procedural SQL that is… Note that there’s not even any joins! (I could if I wanted to… we have a lookup table for gender.)
SELECT VISIT_NUMBER AS “FinNbr”,
CASE
WHEN EVENT_TYPE_CODE = ‘A01’ THEN ‘ADMT’
WHEN EVENT_TYPE_CODE = ‘A02’ THEN ‘XFER’
WHEN EVENT_TYPE_CODE = ‘A03’ THEN ‘DSCH’
WHEN EVENT_TYPE_CODE = ‘A04’ THEN ‘Reg OutP’
WHEN EVENT_TYPE_CODE = ‘A05’ THEN ‘Pre-ADMT’
WHEN EVENT_TYPE_CODE = ‘A08’ THEN ‘UPDT’
WHEN EVENT_TYPE_CODE = ‘A11’ THEN ‘Cancel ADMT’
WHEN EVENT_TYPE_CODE = ‘A12’ THEN ‘Cancel XFER’
WHEN EVENT_TYPE_CODE = ‘A13’ THEN ‘Cancel DSCH’
WHEN EVENT_TYPE_CODE = ‘A17’ THEN ‘Swap’
WHEN EVENT_TYPE_CODE = ‘A38’ THEN ‘Cancel Pre-ADMT’
WHEN EVENT_TYPE_CODE = ‘A28’ THEN ‘A01->Add/Create’
WHEN EVENT_TYPE_CODE = ‘A31’ THEN ‘A08->Update’
WHEN EVENT_TYPE_CODE = ‘A34’ OR EVENT_TYPE_CODE = ‘A40’ THEN ‘Merge/Xref(A34=A40)’
WHEN EVENT_TYPE_CODE = ‘A35’ OR EVENT_TYPE_CODE = ‘A41’ THEN ‘Merge/AcctNo.’
WHEN EVENT_TYPE_CODE = ‘A36’ THEN ‘Merge/Enc.’
WHEN EVENT_TYPE_CODE = ‘A44’ THEN ‘Move Acct Info/Acct No.’
WHEN EVENT_TYPE_CODE = ‘A45’ THEN ‘Move visit Info/Visit No.’
WHEN EVENT_TYPE_CODE = ‘A29’ THEN ‘Cancel Patient No.’
END AS “Tran”,
TO_CHAR(TO_DATE(SUBSTR(MESSAGE_TIME, 1, 14), ‘YYYYMMDDHH24MISS’), ‘YYYY-MON-DD HH:MI’) AS “Activity DT TM”,
(PATIENT_LAST_NAME || ‘, ‘ || PATIENT_FIRST_NAME) AS “Name”,
CASE
WHEN SEX = ‘M’ THEN ‘Male’
WHEN SEX = ‘F’ THEN ‘Female’
END AS “Gender”,
TO_CHAR(TO_DATE(SUBSTR(DATE_OF_BIRTH, 1, 8), ‘YYYYMMDD’), ‘YYYY-MON-DD’) AS “Birth DT”,
PATIENT_IDENTIFIER_LIST AS “MRN Loc”,
POINT_OF_CARE AS “Room”,
BED AS “Bed”,
PATIENT_TYPE AS “PtType”,
HOSPITAL_SERVICE AS “Service”,
TO_CHAR(TO_DATE(SUBSTR(ADMIT_TIME, 1, 14), ‘YYYYMMDDHH24MISS’), ‘YYYY-MON-DD HH:MI’) AS “Admit DT TM”,
TO_CHAR(TO_DATE(SUBSTR(DISCHARGE_TIME, 1, 14), ‘YYYYMMDDHH24MISS’), ‘YYYY-MON-DD HH:MI’) AS “Discharge DT TM”,
ATTENDING_DOCTOR AS “AttendDr”,
–null AS “FinClass”, null AS “User”, null AS “Religion”, null AS “Accom Req”
TO_CHAR(PROCESSED_TIME, ‘YYYY-MON-DD HH:MI’) AS PROCESSED_TIME,
CASE
WHEN Error_ID = 1 THEN ‘RUNTIME’
WHEN Error_ID = 2 THEN ‘EVENT NOT SUPPORTED’
WHEN Error_ID = 3 THEN ‘INTEGRITY SR’
WHEN Error_ID = 4 THEN ‘INTEGRITY ENCOUNTER’
WHEN Error_ID = 5 THEN ‘INTEGRITY ENCOUNTER FC’
WHEN Error_ID = 6 THEN ‘SR ENCOUNTER MISMATCH’
WHEN Error_ID = 7 THEN ‘MISSING MAPPING’
WHEN Error_ID = 8 THEN ‘MISSING UPI’
WHEN Error_ID = 9 THEN ‘MISSING FIRST NAME’
WHEN Error_ID = 10 THEN ‘MISSING LAST NAME’
WHEN Error_ID = 11 THEN ‘MISSING ENCOUNTER NUMBER’
WHEN Error_ID = 12 THEN ‘MISSING FC’
WHEN Error_ID = 13 THEN ‘MISSING ADMISSION DATE’
WHEN Error_ID = 14 THEN ‘MISSING DISCHARGE DATE’
WHEN Error_ID = 15 THEN ‘MISSING PRIOR UPI’
WHEN Error_ID = 16 THEN ‘MISSING PRIOR ENCOUNTER NUMBER’
END AS “Error”,
MAPPING_ERROR_ID
FROM HL7_MESSAGES
CASE
WHEN EVENT_TYPE_CODE = ‘A01’ THEN ‘ADMT’
WHEN EVENT_TYPE_CODE = ‘A02’ THEN ‘XFER’
WHEN EVENT_TYPE_CODE = ‘A03’ THEN ‘DSCH’
WHEN EVENT_TYPE_CODE = ‘A04’ THEN ‘Reg OutP’
WHEN EVENT_TYPE_CODE = ‘A05’ THEN ‘Pre-ADMT’
WHEN EVENT_TYPE_CODE = ‘A08’ THEN ‘UPDT’
WHEN EVENT_TYPE_CODE = ‘A11’ THEN ‘Cancel ADMT’
WHEN EVENT_TYPE_CODE = ‘A12’ THEN ‘Cancel XFER’
WHEN EVENT_TYPE_CODE = ‘A13’ THEN ‘Cancel DSCH’
WHEN EVENT_TYPE_CODE = ‘A17’ THEN ‘Swap’
WHEN EVENT_TYPE_CODE = ‘A38’ THEN ‘Cancel Pre-ADMT’
WHEN EVENT_TYPE_CODE = ‘A28’ THEN ‘A01->Add/Create’
WHEN EVENT_TYPE_CODE = ‘A31’ THEN ‘A08->Update’
WHEN EVENT_TYPE_CODE = ‘A34’ OR EVENT_TYPE_CODE = ‘A40’ THEN ‘Merge/Xref(A34=A40)’
WHEN EVENT_TYPE_CODE = ‘A35’ OR EVENT_TYPE_CODE = ‘A41’ THEN ‘Merge/AcctNo.’
WHEN EVENT_TYPE_CODE = ‘A36’ THEN ‘Merge/Enc.’
WHEN EVENT_TYPE_CODE = ‘A44’ THEN ‘Move Acct Info/Acct No.’
WHEN EVENT_TYPE_CODE = ‘A45’ THEN ‘Move visit Info/Visit No.’
WHEN EVENT_TYPE_CODE = ‘A29’ THEN ‘Cancel Patient No.’
END AS “Tran”,
TO_CHAR(TO_DATE(SUBSTR(MESSAGE_TIME, 1, 14), ‘YYYYMMDDHH24MISS’), ‘YYYY-MON-DD HH:MI’) AS “Activity DT TM”,
(PATIENT_LAST_NAME || ‘, ‘ || PATIENT_FIRST_NAME) AS “Name”,
CASE
WHEN SEX = ‘M’ THEN ‘Male’
WHEN SEX = ‘F’ THEN ‘Female’
END AS “Gender”,
TO_CHAR(TO_DATE(SUBSTR(DATE_OF_BIRTH, 1, 8), ‘YYYYMMDD’), ‘YYYY-MON-DD’) AS “Birth DT”,
PATIENT_IDENTIFIER_LIST AS “MRN Loc”,
POINT_OF_CARE AS “Room”,
BED AS “Bed”,
PATIENT_TYPE AS “PtType”,
HOSPITAL_SERVICE AS “Service”,
TO_CHAR(TO_DATE(SUBSTR(ADMIT_TIME, 1, 14), ‘YYYYMMDDHH24MISS’), ‘YYYY-MON-DD HH:MI’) AS “Admit DT TM”,
TO_CHAR(TO_DATE(SUBSTR(DISCHARGE_TIME, 1, 14), ‘YYYYMMDDHH24MISS’), ‘YYYY-MON-DD HH:MI’) AS “Discharge DT TM”,
ATTENDING_DOCTOR AS “AttendDr”,
–null AS “FinClass”, null AS “User”, null AS “Religion”, null AS “Accom Req”
TO_CHAR(PROCESSED_TIME, ‘YYYY-MON-DD HH:MI’) AS PROCESSED_TIME,
CASE
WHEN Error_ID = 1 THEN ‘RUNTIME’
WHEN Error_ID = 2 THEN ‘EVENT NOT SUPPORTED’
WHEN Error_ID = 3 THEN ‘INTEGRITY SR’
WHEN Error_ID = 4 THEN ‘INTEGRITY ENCOUNTER’
WHEN Error_ID = 5 THEN ‘INTEGRITY ENCOUNTER FC’
WHEN Error_ID = 6 THEN ‘SR ENCOUNTER MISMATCH’
WHEN Error_ID = 7 THEN ‘MISSING MAPPING’
WHEN Error_ID = 8 THEN ‘MISSING UPI’
WHEN Error_ID = 9 THEN ‘MISSING FIRST NAME’
WHEN Error_ID = 10 THEN ‘MISSING LAST NAME’
WHEN Error_ID = 11 THEN ‘MISSING ENCOUNTER NUMBER’
WHEN Error_ID = 12 THEN ‘MISSING FC’
WHEN Error_ID = 13 THEN ‘MISSING ADMISSION DATE’
WHEN Error_ID = 14 THEN ‘MISSING DISCHARGE DATE’
WHEN Error_ID = 15 THEN ‘MISSING PRIOR UPI’
WHEN Error_ID = 16 THEN ‘MISSING PRIOR ENCOUNTER NUMBER’
END AS “Error”,
MAPPING_ERROR_ID
FROM HL7_MESSAGES
Probably going to take this and make it into a view once we’re happy with the output.
I also want to note that this is not a schema designed by me. In fact, we don’t know who came up with it, but we’re stuck until we do some serious rewrites in the apps.