HI thx for the info here's the code
;with CteGetMain as (
SELECT DISTINCT PAT.PAT_NAME as PatName,
PAT.PAT_MRN_ID as MRN,
ZBASE.NAME as PatBaseClass,
ZCLASS.NAME as PatSubClass,
HSP.HOSP_ADMSN_TIME as AdmissionDate,
HSP.HOSP_DISCH_TIME as DischargeDate,
DATEDIFF(DAY, HOSP_ADMSN_TIME, HOSP_DISCH_TIME) as VisitLength,
CASE
when
QST.ORD_QUEST_RESP = 'Admit from OB Triage' then 1
else 0
END AS is_AdmitOBTriage,
ORP3.ORDERING_MODE_C as OrderMode,
VHX.EVENT_TYPE_C as EventCode,
DEP.DEPARTMENT_NAME as DeptName,
HSP.PAT_ENC_CSN_ID as CSN,
ORP.PROC_CODE as ProcCode,
EAP.PROC_NAME as ProcName,
MET.ORDER_DTTM as OrderDate,
MET.ORDER_ID as OrderID,
ZTHERA.THERA_CLASS_C as TheraClass,
MED.ORDER_MED_ID as MedOrderID,
MED.DISPLAY_NAME AS MedName,
ATND.PROV_ID as AtndProvID,
SER.PROV_NAME as AtndProv,
ATND.LINE as AtndLine,
SERO.PROV_ID OrderProvID,
SERO.PROV_NAME as OrderProv,
SER.PROV_ID AuthID,
SERA.PROV_NAME as AuthProv,
case
When ORP.ORDER_TYPE_C = 9
or ORP.PROC_CODE in ('DNS3', 'DNS2', 'NUR519', 'NUR519', 'NUR1081', 'NUR1019',
'NUR305', 'NUR981', 'NUR919', 'Diet87' )
Then 'Diet'
When ORP.PROC_CODE in ('ADT1')
Then 'Admit'
When ORP.PROC_CODE in ( 'NUR9','NUR162', 'NUR87', 'NUR158', 'NUR25', 'NUR1029', 'NUR11', 'NUR129',
'NUR130', 'NUR44', 'NUR371', 'NUR511 ','NUR84','PRV9', 'NUR940','NUR25',
'NUR533','NUR714','NUR79', 'NUR820','NUR52')
Then 'Activity'
When ORP.PROC_CODE in ( 'COD1', 'COD2', 'COD3')
Then 'Code'
When ZTHERA.THERA_CLASS_C in (1,2,6)
Then 'Pain'
END as OrderType
FROM V_PAT_ADT_LOCATION_HX VHX
inner join PAT_ENC_HSP HSP
ON VHX.PAT_ENC_CSN = HSP.PAT_ENC_CSN_ID
inner join CLARITY_DEP DEP
ON VHX.ADT_DEPARTMENT_ID = DEP.DEPARTMENT_ID
inner join PATIENT PAT
ON HSP.PAT_ID = PAT.PAT_ID
LEFT OUTER JOIN ORDER_METRICS MET
ON HSP.PAT_ENC_CSN_ID = MET.PAT_ENC_CSN_ID
LEFT OUTER JOIN HSP_ATND_PROV ATND
ON HSP.PAT_ENC_CSN_ID = ATND.PAT_ENC_CSN_ID
LEFT OUTER JOIN ORDER_MED MED
ON MET.PAT_ENC_CSN_ID = MED.PAT_ENC_CSN_ID
LEFT OUTER JOIN ORDER_PROC ORP
ON MET.ORDER_ID = ORP.ORDER_PROC_ID
LEFT OUTER JOIN CLARITY_SER SERO
ON MET.ORDERING_PROV_ID = SERO.PROV_ID
LEFT OUTER JOIN CLARITY_SER SERA
ON MET.AUTH_PROV_ID = SERA.PROV_ID
LEFT OUTER JOIN ORDER_PROC_3 ORP3
ON ORP.ORDER_PROC_ID = ORP3.ORDER_ID
LEFT OUTER JOIN CLARITY_MEDICATION CMED
ON MED.MEDICATION_ID = CMED.MEDICATION_ID
LEFT OUTER JOIN ZC_THERA_CLASS ZTHERA
ON CMED.THERA_CLASS_C = ZTHERA.THERA_CLASS_C
LEFT OUTER JOIN CLARITY_SER SER
ON ATND.PROV_ID = SER.PROV_ID
Left outer join CLARITY_EAP EAP
ON ORP.PROC_ID = EAP.PROC_ID
Left join HSP_ACCOUNT HACCT
on HSP.HSP_ACCOUNT_ID = HACCT.HSP_ACCOUNT_ID
LEFT JOIN ZC_ACCT_BASECLS_HA ZBASE
on HACCT.ACCT_BASECLS_HA_C = ZBASE.ACCT_BASECLS_HA_C
Left join ZC_ACCT_CLASS_HA ZClass
on HACCT.ACCT_CLASS_HA_C = ZClass.ACCT_CLASS_HA_C
Left join ORD_SPEC_QUEST QST
on ORP.ORDER_PROC_ID = QST.ORDER_ID --and QST.ORD_QUEST_ID = '100795' and QST.ORD_QUEST_RESP <> 'ADMIT from OB Triage'
WHERE DEP.DEPARTMENT_NAME not in ('MMC ED ADULT','MMC ED BURN','MMC ED PEDS')
AND VHX.EVENT_TYPE_C in (1,4,7) -- Admission, Transfer out, Outpatient
AND ORP3.ORDERING_MODE_C = 2 -- inpatient order mode
AND HSP.HOSP_ADMSN_TIME >= {ts '2013-08-01 00:00:00'}
AND HSP.HOSP_ADMSN_TIME <{ts '2013-09-01 00:00:00'}
AND ATND.LINE = 1 and HSP.PAT_ENC_CSN_ID = '7847189'
)
/* Now that we have identified the orders we want in OrderType lets remove all the unnecessary orders
marked as null so we create a smaller results set for faster processing */
, CTERemoveNulls as (
select CteGetMain.*
from CteGetMain
where (OrderType is not null))
select * from CTERemoveNulls