Mẫu dữ liệu hành trình bệnh nhân

/* MEDITECH Data Repository T-SQL Extraction for ProcessMind */
/* Process: Patient Journey */
/* Dialect: T-SQL */

WITH BaseVisits AS (
    /* Define the population: Discharged patients within a date range */
    SELECT 
        V.VisitID,
        V.PatientID,
        V.AccountNumber AS MedicalRecordNumber,
        V.AdmitDateTime,
        V.DischargeDateTime,
        V.FacilityID,
        V.PatientType,
        V.AttendingProviderID,
        V.DischargeDisposition,
        NULLIF(DATEDIFF(MINUTE, V.AdmitDateTime, V.DischargeDateTime), 0) / 1440.0 AS LengthOfStay,
        /* Flag readmissions logic would go here, simplified as 0 for base script */
        0 AS IsReadmission
    FROM 
        [YourDatabaseName].[dbo].[AdmVisits] V
    WHERE 
        V.DischargeDateTime >= '2023-01-01' 
        AND V.DischargeDateTime < '2023-04-01'
        AND V.Status = 'DIS' /* Discharged Status */
),

PatientDiagnoses AS (
    /* Helper CTE for Primary Diagnosis to avoid duplicates in joins */
    SELECT 
        D.VisitID,
        MAX(D.ICDCode) AS PrimaryDiagnosis
    FROM 
        [YourDatabaseName].[dbo].[AbsDiagnoses] D
    WHERE 
        D.Rank = 1 /* Primary Diagnosis Rank */
    GROUP BY 
        D.VisitID
),

TriageData AS (
    /* Helper CTE for Triage Acuity */
    SELECT
        T.VisitID,
        MAX(T.AcuityLevel) AS TriageAcuityLevel
    FROM
        [YourDatabaseName].[dbo].[EdTriage] T
    GROUP BY
        T.VisitID
)

/* 1. Patient Registered */
SELECT 
    V.VisitID AS PatientEpisode,
    'Patient Registered' AS ActivityName,
    V.AdmitDateTime AS EventTimestamp,
    'MEDITECH_ADM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    V.FacilityID AS HospitalDepartment,
    V.AttendingProviderID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    BaseVisits V
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    V.AdmitDateTime IS NOT NULL

UNION ALL

/* 2. Triage Completed */
SELECT 
    V.VisitID AS PatientEpisode,
    'Triage Completed' AS ActivityName,
    ED.TriageDateTime AS EventTimestamp,
    'MEDITECH_ED' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    'Emergency Department' AS HospitalDepartment,
    ED.TriageNurseID AS AttendingProvider,
    V.PatientType,
    ED.AcuityLevel AS TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[EdTriage] ED
    INNER JOIN BaseVisits V ON ED.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
WHERE 
    ED.TriageDateTime IS NOT NULL

UNION ALL

/* 3. Order Placed (General) */
SELECT 
    V.VisitID AS PatientEpisode,
    'Order Placed' AS ActivityName,
    O.OrderDateTime AS EventTimestamp,
    'MEDITECH_OE' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    O.Department AS HospitalDepartment,
    O.OrderingProviderID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[OeOrders] O
    INNER JOIN BaseVisits V ON O.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    O.Category NOT IN ('CONSULT', 'DISCHARGE') /* Exclude specific types handled elsewhere */

UNION ALL

/* 4. Specimen Collected */
SELECT 
    V.VisitID AS PatientEpisode,
    'Specimen Collected' AS ActivityName,
    L.CollectionDateTime AS EventTimestamp,
    'MEDITECH_LAB' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    'Laboratory' AS HospitalDepartment,
    L.CollectedBy AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[LabSpecimens] L
    INNER JOIN BaseVisits V ON L.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    L.CollectionDateTime IS NOT NULL

UNION ALL

/* 5. Diagnostic Result Verified */
SELECT 
    V.VisitID AS PatientEpisode,
    'Diagnostic Result Verified' AS ActivityName,
    R.VerifiedDateTime AS EventTimestamp,
    'MEDITECH_LAB' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    'Laboratory' AS HospitalDepartment,
    R.VerifiedBy AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[LabResults] R
    INNER JOIN BaseVisits V ON R.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    R.VerifiedDateTime IS NOT NULL

UNION ALL

/* 6. Diagnosis Documented */
SELECT 
    V.VisitID AS PatientEpisode,
    'Diagnosis Documented' AS ActivityName,
    DX.EntryDateTime AS EventTimestamp,
    'MEDITECH_ABS' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    V.FacilityID AS HospitalDepartment,
    DX.ProviderID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[AbsDiagnoses] DX
    INNER JOIN BaseVisits V ON DX.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    DX.EntryDateTime IS NOT NULL

UNION ALL

/* 7. Care Plan Initiated */
SELECT 
    V.VisitID AS PatientEpisode,
    'Care Plan Initiated' AS ActivityName,
    N.CreateDateTime AS EventTimestamp,
    'MEDITECH_NUR' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    N.NurseUnit AS HospitalDepartment,
    N.NurseID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[NurPlan] N
    INNER JOIN BaseVisits V ON N.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    N.CreateDateTime IS NOT NULL

UNION ALL

/* 8. Medication Administered */
SELECT 
    V.VisitID AS PatientEpisode,
    'Medication Administered' AS ActivityName,
    M.AdminDateTime AS EventTimestamp,
    'MEDITECH_PHA' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    M.AdminLocation AS HospitalDepartment,
    M.AdministeredBy AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[PhaMedAdmin] M
    INNER JOIN BaseVisits V ON M.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    M.Status = 'ADMINISTERED'

UNION ALL

/* 9. Consult Request Sent */
SELECT 
    V.VisitID AS PatientEpisode,
    'Consult Request Sent' AS ActivityName,
    O.OrderDateTime AS EventTimestamp,
    'MEDITECH_OE' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    O.Department AS HospitalDepartment,
    O.OrderingProviderID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[OeOrders] O
    INNER JOIN BaseVisits V ON O.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    O.Category = 'CONSULT'

UNION ALL

/* 10. Consultation Completed */
SELECT 
    V.VisitID AS PatientEpisode,
    'Consultation Completed' AS ActivityName,
    O.CompletedDateTime AS EventTimestamp,
    'MEDITECH_OE' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    O.Department AS HospitalDepartment,
    O.OrderingProviderID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[OeOrders] O
    INNER JOIN BaseVisits V ON O.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    O.Category = 'CONSULT' 
    AND O.Status = 'COMPLETED' 
    AND O.CompletedDateTime IS NOT NULL

UNION ALL

/* 11. Patient Transferred */
SELECT 
    V.VisitID AS PatientEpisode,
    'Patient Transferred' AS ActivityName,
    TX.TransferDateTime AS EventTimestamp,
    'MEDITECH_ADM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    TX.ToLocation AS HospitalDepartment,
    NULL AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[AdmRoomTx] TX
    INNER JOIN BaseVisits V ON TX.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    TX.TransferDateTime IS NOT NULL

UNION ALL

/* 12. Discharge Order Written */
SELECT 
    V.VisitID AS PatientEpisode,
    'Discharge Order Written' AS ActivityName,
    O.OrderDateTime AS EventTimestamp,
    'MEDITECH_OE' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    O.Department AS HospitalDepartment,
    O.OrderingProviderID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[OeOrders] O
    INNER JOIN BaseVisits V ON O.VisitID = V.VisitID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    O.Category = 'DISCHARGE'
    OR O.Mnemonic LIKE '%DISCHARGE%'

UNION ALL

/* 13. Patient Discharged */
SELECT 
    V.VisitID AS PatientEpisode,
    'Patient Discharged' AS ActivityName,
    V.DischargeDateTime AS EventTimestamp,
    'MEDITECH_ADM' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    V.FacilityID AS HospitalDepartment,
    V.AttendingProviderID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    BaseVisits V
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    V.DischargeDateTime IS NOT NULL

UNION ALL

/* 14. Follow-up Booked */
SELECT 
    V.VisitID AS PatientEpisode,
    'Follow-up Booked' AS ActivityName,
    S.BookDateTime AS EventTimestamp,
    'MEDITECH_SCH' AS SourceSystem,
    GETDATE() AS LastDataUpdate,
    V.MedicalRecordNumber,
    S.ApptDepartment AS HospitalDepartment,
    S.ProviderID AS AttendingProvider,
    V.PatientType,
    T.TriageAcuityLevel,
    D.PrimaryDiagnosis,
    V.DischargeDisposition,
    V.LengthOfStay,
    V.IsReadmission
FROM 
    [YourDatabaseName].[dbo].[SchAppt] S
    INNER JOIN BaseVisits V ON S.PatientID = V.PatientID
    LEFT JOIN PatientDiagnoses D ON V.VisitID = D.VisitID
    LEFT JOIN TriageData T ON V.VisitID = T.VisitID
WHERE 
    S.BookDateTime > V.AdmitDateTime
    AND S.BookDateTime <= DATEADD(day, 30, V.DischargeDateTime) /* Logic to link appt to episode */
    AND S.Status NOT IN ('CANCELLED', 'NOSHOW');