October 22, 2014 at 9:19 am
I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.
I really have not an idea on how to complete something like this and my google searches have been fruitless.
I attached a spreadsheet with an example of what I am getting.
SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',
TPM300_PAT_VISIT.vst_ext_id AS 'Visit ID',
TSM180_MST_COD_DTL.cod_dtl_ext_id AS 'Patient Type',
TSM180_MST_COD_DTL.cod_dtl_ds,
TPM300_PAT_VISIT.adm_ts AS 'Admit TS',
TPM300_PAT_VISIT.dschrg_ts,
TRX101_THERAPY_ITEM.dug_ds AS 'Drug Desc',
TRX101_THERAPY_ITEM.bnd_nm AS 'Brand Name'
FROM
TPM300_PAT_VISIT
LEFT JOIN TRX100_THERAPY_ORDER
ON TPM300_PAT_VISIT.vst_int_id = TRX100_THERAPY_ORDER.vst_int_id
RIGHT JOIN TRX101_THERAPY_ITEM
ON TRX101_THERAPY_ITEM.prx_int_id = TRX100_THERAPY_ORDER.prx_int_id
INNER JOIN TCP500_RX_ADS
ON TCP500_RX_ADS.prx_int_id = TRX101_THERAPY_ITEM.prx_int_id
INNER JOIN TSM180_MST_COD_DTL
ON TPM300_PAT_VISIT.pat_ty = TSM180_MST_COD_DTL.cod_dtl_int_id
INNER JOIN TSM040_PERSON_HDR
ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id
October 22, 2014 at 10:09 am
Can you tell us how you know which is the current patient type and what defines a distinct visit in this scenario? I think something like this might work, uses a Common Table Expression (CTE) and the ROW_NUMBER function):
WITH visits
AS (
SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS LastName,
TSM040_PERSON_HDR.fst_nm AS FirstName,
TPM300_PAT_VISIT.vst_ext_id AS VisitID,
TSM180_MST_COD_DTL.cod_dtl_ext_id AS PatientType,
TSM180_MST_COD_DTL.cod_dtl_ds,
TPM300_PAT_VISIT.adm_ts AS AdmitTS,
TPM300_PAT_VISIT.dschrg_ts,
TRX101_THERAPY_ITEM.dug_ds AS DrugDesc,
TRX101_THERAPY_ITEM.bnd_nm AS BrandName,
ROW_NUMBER() OVER (PARTITION BY TPM300_PAT_VISIT.med_rec_no ORDER BY TPM300_PAT_VISIT.adm_ts) AS rowNo
FROM
TPM300_PAT_VISIT
LEFT JOIN TRX100_THERAPY_ORDER
ON TPM300_PAT_VISIT.vst_int_id = TRX100_THERAPY_ORDER.vst_int_id
RIGHT JOIN TRX101_THERAPY_ITEM
ON TRX101_THERAPY_ITEM.prx_int_id = TRX100_THERAPY_ORDER.prx_int_id
INNER JOIN TCP500_RX_ADS
ON TCP500_RX_ADS.prx_int_id = TRX101_THERAPY_ITEM.prx_int_id
INNER JOIN TSM180_MST_COD_DTL
ON TPM300_PAT_VISIT.pat_ty = TSM180_MST_COD_DTL.cod_dtl_int_id
INNER JOIN TSM040_PERSON_HDR
ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id
)
SELECT
V1.med_rec_no,
V1.FirstName,
V1.LastName,
V1.VisitID AS OriginalVisitID
V2.VisitID AS NewVisitID,
V1.PatientType AS OriginalPatientType,
V2.PatientType AS NewPatientType,
V1.cod_dtl_ds AS OriginalCodDtlDs,
V2.cod_dlt_ds AS NewCodDtlDs,
V1.AdmitTS AS OriginalAdmitTs,
V2.AdmitTS AS NewAdmitTs,
V1.dschrg_ts AS OriginalDischargeTs,
V2.dschrg_ts AS NewDischargeTs,
V1.DrugDesc,
V1.BrandName
FROM
visits AS V1 LEFT JOIN
visits AS V2 ON
V1.med_rec_no = V2.med_rec_no AND
V1.rowNo + 1 = V2.rowNo
WHERE
V1.rowNo = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2014 at 10:27 am
Jack Corbett (10/22/2014)
Current Patient Type would be 1 or Acute Patient
Follow on Type would be 2 or Swingbed
A distinct visit is determined by the Visit ID
October 22, 2014 at 11:47 am
Thanks Jack it worked.
October 22, 2014 at 12:36 pm
Your welcome. Just note that is only works for 2 rows per med_rec_no and doesn't take into account the likelihood for a gap in times. The code assumes that if there are 2 rows for a med_rec_no that the 1st rows discharge date will be the 2nd rows admission date.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2014 at 12:56 pm
If I understand correctly, if a patient comes back later in the month we won't see this visit?
October 22, 2014 at 1:50 pm
It depends. The way the current code I provided works it just links the first 2 rows for a specific med_rec_no regardless of gap. So if I have something like this:
med_rec_no Patient Type AdmitDate DischargeDate
1 John Smith 1 1/1/2014 13:00 1/1/2014 17:00
1 John Smith 2 2/1/2014 09:00 2/2/2014 11:00
My code would return:
med_rec_no Patient OrigType NewType OrigAdmitDate NewAdmitDate OrigDischargeDate NewDischargeDate
1 John Smith 1 2 1/1/2014 13:00 2/1/2014 09:00 1/1/2014 17:00 2/2/2014 11:00
Since this is likely 2 distinct visits you probably don't want that so you may want to add to the join that V1.DischargeDate = V2.AdmitDate, possibly with some fudge factor if it takes a few minutes for the new admission to take place after the "discharge"
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply