Skip to Content
0
Former Member
Jan 22, 2015 at 07:03 PM

Trend data?

32 Views

I would like to trend the data below. How can I accomplish this with crystal.

Data that I would like to trend:

Year to date: Average daily census per month

Current Month: Average daily census per day for that month

Year to date: Average daily census by day of week

Current month: Average daily census by day of week


CMD Line


SELECT DISTINCT
V_FV_MRN.MRN,
V_FV_MRN.PAT_NAME,
CLARITY_BED.BED_LABEL,
ZC_PAT_CLASS.NAME,
HSP_TRTMT_TEAM.TRTMNT_TM_BEGIN_DT,
HSP_TRTMT_TEAM.TRTMNT_TM_END_DT,
PAT_ENC_HSP.HOSP_DISCH_TIME,
ZC_TRTMT_TEAM_REL.NAME as treat_name,
CLARITY_SER.PROV_NAME,
HSP_TRTMT_TEAM.PROV_ID,
HSP_ACCOUNT.LOC_ID,
PATIENT.BIRTH_DATE,
HSP_ACCOUNT.ADM_DATE_TIME,
CLARITY_SER_admitting.PROV_NAME

FROM EPICADM.CLARITY_SER CLARITY_SER

LEFT OUTER JOIN EPICADM.HSP_TRTMT_TEAM HSP_TRTMT_TEAM
ON CLARITY_SER.PROV_ID=HSP_TRTMT_TEAM.PROV_ID
and HSP_TRTMT_TEAM.TRTMNT_TM_BEGIN_DT >= (trunc(sysdate -10) + 24/24)
and HSP_TRTMT_TEAM.TRTMNT_TM_BEGIN_DT<= (trunc(sysdate -1) + 07/24)
and (HSP_TRTMT_TEAM.TRTMNT_TM_END_DT is null or HSP_TRTMT_TEAM.TRTMNT_TM_END_DT> (trunc(sysdate -1) + 07/24))

LEFT OUTER JOIN EPICADM.PAT_ENC_HSP PAT_ENC_HSP
ON PAT_ENC_HSP.PAT_ENC_CSN_ID=HSP_TRTMT_TEAM.PAT_ENC_CSN_ID

LEFT OUTER JOIN EPICADM.CLARITY_BED CLARITY_BED
ON CLARITY_BED.BED_ID=PAT_ENC_HSP.BED_ID

LEFT OUTER JOIN EPICADM.CLARITY_SER CLARITY_SER_admitting
ON PAT_ENC_HSP.ADMISSION_PROV_ID=CLARITY_SER_admitting.PROV_ID

INNER JOIN EPICADM.ZC_PAT_CLASS ZC_PAT_CLASS
ON PAT_ENC_HSP.ADT_PAT_CLASS_C=ZC_PAT_CLASS.ADT_PAT_CLASS_C

RIGHT OUTER JOIN EPICADM.HSP_ACCOUNT HSP_ACCOUNT
ON HSP_ACCOUNT.PRIM_ENC_CSN_ID=HSP_TRTMT_TEAM.PAT_ENC_CSN_ID
and HSP_ACCOUNT.LOC_ID=410 --Southdale Location

INNER JOIN EPICADM.V_FV_MRN V_FV_MRN
ON HSP_TRTMT_TEAM.PAT_ID=V_FV_MRN.PAT_ID

RIGHT OUTER JOIN EPICADM.PATIENT PATIENT
ON PATIENT.PAT_ID=V_FV_MRN.PAT_ID

LEFT OUTER JOIN EPICADM.ZC_TRTMT_TEAM_REL ZC_TRTMT_TEAM_REL
ON HSP_TRTMT_TEAM.TRTMNT_TEAM_REL_C=ZC_TRTMT_TEAM_REL.TRTMNT_TEAM_REL_C

where ("ZC_TRTMT_TEAM_REL"."NAME"='Hospitalist')
and ( "PAT_ENC_HSP"."HOSP_DISCH_TIME" is null or "PAT_ENC_HSP"."HOSP_DISCH_TIME" > (trunc(sysdate -1) + 07/24))