-- Identify discharged Episodes and ward Stays
; WITH Dis AS
(
select Episode_ID from [hl7].[vw_CNS_PatientVisit]
Where PV_ID LIKE '%-A03'
--and PV_ID LIKE '%21261%'
)
-- Identify Admissions for episodes not discharged
, A01 as
(
select REPLACE(PV_ID,'-A01','') ID,
from [hl7].[vw_CNS_PatientVisit]
where Episode_ID NOT IN (SELECT Episode_ID from Dis)
AND PV_ID LIKE '%-A01'
)
-- Identify Transfer for episodes not discharged
, A02 as
(
select REPLACE(PV_ID,'-A02','') ID,
from [hl7].[vw_CNS_PatientVisit]
where Episode_ID NOT IN (SELECT Episode_ID from Dis)
AND PV_ID LIKE '%-A02'
)
, AllOpenWardStays as
(
select from A01
UNION
select from A02
)
-- Identify discharged Episodes and ward Stays ; WITH Dis AS ( select Episode_ID from [hl7].[vw_CNS_PatientVisit] Where PV_ID LIKE '%-A03' --and PV_ID LIKE '%21261%' ) -- Identify Admissions for episodes not discharged , A01 as ( select REPLACE(PV_ID,'-A01','') ID, from [hl7].[vw_CNS_PatientVisit] where Episode_ID NOT IN (SELECT Episode_ID from Dis) AND PV_ID LIKE '%-A01' ) -- Identify Transfer for episodes not discharged , A02 as ( select REPLACE(PV_ID,'-A02','') ID, from [hl7].[vw_CNS_PatientVisit] where Episode_ID NOT IN (SELECT Episode_ID from Dis) AND PV_ID LIKE '%-A02' ) , AllOpenWardStays as ( select from A01 UNION select from A02 )
INSERT INTO [hl7].[tblPatientVisit] ( [PV_ID] ,[SourceSystem] ,[MPI_ID] ,[Ward_Stay_ID] ,[Patient_ID] ,[Patient_Class] ,[Ward_LocationID] ,[Admission_Type] ,[Consulting_GMC_Code] ,[Consulting_Surname] ,[Consulting_Forename] ,[Admit_Source] ,[Episode_ID] ,[Discharge_Method] ,[Discharge_Destination_ID] ,[PV_Start] ,[PV_End] ,[Event_Type] ,[WardStay_Updated_Dttm] ) select [PV_ID] ,'MH' ,[MPI_ID] ,[Ward_Stay_ID] ,[Patient_ID] ,[Patient_Class] ,[Ward_LocationID] ,[Admission_Type] ,[Consulting_GMC_Code] ,[Consulting_Surname] ,[Consulting_Forename] ,[Admit_Source] ,[Episode_ID] ,[Discharge_Method] ,[Discharge_Destination_ID] ,[PV_Start] ,[PV_End] ,[Event_Type] ,[WardStay_Updated_Dttm] from AllOpenWardStays WHERE MPI_ID IS NOT NULL