New to SQL - How to compare two rows and two different columns

  • 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

  • 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 (10/22/2014)


    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):

    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

  • Thanks Jack it worked.

  • 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.

  • If I understand correctly, if a patient comes back later in the month we won't see this visit?

  • 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"

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply