RWEMS

/*POORLY WRITTEN, IN A HURRY-SHOULD RE-WRITE TO CALL PATIENT INFORMATION, THEN CTE FOR COVID, THEN CALL BOTH QUERIES TOGETHER*/
SELECT N.[PERSON_ID]
      ,N.[ENCNTR_ID]
      ,N.[MRN]
      ,N.[VISIT]
	  ,P.[NAME_LAST]
      ,P.[NAME_FIRST]
/*    ,P.[NAME_MIDDLE]
      ,N.[EVENT_ID]
      ,N.[EVENT_CD]
      ,N.[EVENT_CD_DISPLAY]
      ,N.[EVENT_SET_KEY_NAME]
      ,N.[DISP1]
      ,N.[DISP2]
      ,N.[DISP3]
      ,N.[DISP4]*/
      ,N.[EVENT_END_DT_TM]ARRIVAL
      ,N.[EVENT_TAG]MODE
      ,N.[EVENT_TITLE_TEXT]DOCUMENTED_FROM
	  ,L.[EVENT_CD_DISPLAY]LAB_TEST
	  ,L.[RESULT_VAL]COVIDRESULT
	  ,L.[EVENT_END_DT_TM]LAB_RESULT
  FROM ((([CDM].[dbo].[CDM_NURSING_DOC] AS N
		JOIN [CDM].[dbo].[CDM_PATIENT] AS P ON N.[PERSON_ID]=P.[PERSON_ID])
		JOIN [CDM].[dbo].[CDM_LAB] AS L ON N.[PERSON_ID]=L.[PERSON_ID])
		JOIN [CDM].[dbo].[CDM_ENCOUNTER] AS E ON N.[ENCNTR_ID]=E.[ENCNTR_ID])
  where N.[EVENT_END_DT_TM]>=DATEADD(HOUR, -72, GETDATE()) 
		AND E.[ORGANIZATION]='Virginia Mason Downtown Medical Center'		
		AND ((N.[DISP1]='Admission Hx & Information' 
		AND N.[DISP2]='ED General Information' 
		AND N.[DISP3]='MODE OF ARRIVAL'
		AND (N.[EVENT_TAG]='AMBULANCE' OR N.[EVENT_TAG]='MEDIC'OR N.[EVENT_TAG]='AIRLIFT'))
		OR (N.[DISP1]='ED Documentation'
		AND N.[DISP2]='ED Triage Information'
		AND N.[DISP3]='Lynx Mode of Arrival'
		AND (N.[EVENT_TAG]='AMBULANCE' OR N.[EVENT_TAG]='MEDIC'OR N.[EVENT_TAG]='AIRLIFT')))
		AND L.[EVENT_CD_DISPLAY]='SARS-CoV-2 (COVID-19) Qual PCR Result' 
		AND L.[RESULT_VAL]='DETECTED'
		AND L.[EVENT_END_DT_TM]>=DATEADD(DAY,-14, N.[EVENT_END_DT_TM])
		group by N.[PERSON_ID], N.[ENCNTR_ID] , N.[MRN], N.[VISIT], P.[NAME_LAST]
      ,P.[NAME_FIRST], N.[EVENT_END_DT_TM], N.[EVENT_TAG],N.[EVENT_TITLE_TEXT],L.[EVENT_CD_DISPLAY]
	  ,L.[RESULT_VAL],L.[EVENT_END_DT_TM]
	  ORDER BY N.[EVENT_END_DT_TM]DESC;