Skip to Content
0

Beginner needs help with a pie chart

Nov 16, 2016 at 05:23 PM

41

avatar image

I have a crystal report and I need to create a pie chart based on the following: The % of outliers out of the total patients.

I used running totals for all my totals. I do not have any grouping. I have copied my SQL that is in the command object inside Crystal:


-- Purpose: Have a report to show percentage of Patients that are outliers monthly and also a percentage per wound type

-- Vars

declare @StartDate as datetime = EPIC_UTIL.EFN_DIN('{?Start Date}');
declare @EndDate as datetime = dateadd(s, 86399, EPIC_UTIL.EFN_DIN('{?End Date}'));

DECLARE @PatientStart AS DATETIME = EPIC_UTIL.EFN_DIN('Y-1');
DECLARE @PatientEnd AS DATETIME = dateadd(s, 86399, EPIC_UTIL.EFN_DIN('T-1'))

DECLARE @HEALING_PERCENTAGE AS NUMERIC(4,2) = 49.99;
DECLARE @WEEKS AS INT = 4;
DECLARE @TOTAL_WEEKS AS INT = 14;


-- CTEs


WITH
PATIENT_LIST AS (
SELECT DISTINCT PAT_ID FROM PAT_ENC
--WHERE DEPARTMENT_ID = @DEPT_ID
WHERE ((0 in {?Department}) or (PAT_ENC.DEPARTMENT_ID in {?Department}))
AND CONTACT_DATE BETWEEN @PatientStart AND @PatientEnd
),

MAIN AS (
SELECT
ROW_NUMBER() OVER(PARTITION BY IP_LDA_NOADDSINGLE.IP_LDA_ID ORDER BY IP_FLWSHT_MEAS.RECORDED_TIME DESC) AS [ROW]
, PATIENT.PAT_NAME AS [Patient Name]
, PATIENT.PAT_MRN_ID AS [MRN]
, IP_LDA_NOADDSINGLE.PAT_ID AS [PAT_ID]
, IP_FLOWSHEET_ROWS.INPATIENT_DATA_ID
, IP_LDA_NOADDSINGLE.PAT_ENC_CSN_ID AS [LDA Enc CSN]

, CASE WHEN CLARITY_SER.PROV_NAME IS NULL THEN
ADMIT_SER.PROV_NAME
ELSE
CLARITY_SER.PROV_NAME
END AS [LDA Enc Provider]

, IP_LDA_NOADDSINGLE.IP_LDA_ID AS [LDA_ID]
, IP_FLWSHT_REC.FSD_ID AS [FSD_ID]
-- , IP_FLO_GP_DATA.DISP_NAME AS [LDA Group]

, IP_FLWSHT_MEAS.RECORDED_TIME AS [LDA Healing Date]
, IP_LDA_NOADDSINGLE.[PROPERTIES_DISPLAY]
, IP_FLOWSHEET_ROWS.FLOWSHT_ROW_NAME AS [LDA Row Name]

, CASE WHEN ISNUMERIC(IP_FLWSHT_MEAS.MEAS_VALUE) = 1 THEN
CASE WHEN CHARINDEX('.',IP_FLWSHT_MEAS.MEAS_VALUE) <> 0 THEN
CAST(LEFT(IP_FLWSHT_MEAS.MEAS_VALUE,CHARINDEX('.',IP_FLWSHT_MEAS.MEAS_VALUE)+2) AS NUMERIC(12,2))
ELSE
IP_FLWSHT_MEAS.MEAS_VALUE
END
ELSE
NULL
END AS [Healing Percentage]
, PAT_ENC_HSP.DEPARTMENT_ID

,
CASE WHEN IP_FLO_GP_DATA.DISP_NAME = 'Other Ulcers' THEN
(SELECT TOP 1 IP_FLWSHT_MEAS2.MEAS_VALUE
FROM IP_LDA_NOADDSINGLE IP_LDA_NOADDSINGLE2
JOIN IP_FLOWSHEET_ROWS IP_FLOWSHEET_ROWS2 ON IP_LDA_NOADDSINGLE2.IP_LDA_ID = IP_FLOWSHEET_ROWS2.IP_LDA_ID
JOIN IP_FLWSHT_REC IP_FLWSHT_REC2 ON IP_FLOWSHEET_ROWS2.INPATIENT_DATA_ID = IP_FLWSHT_REC2.INPATIENT_DATA_ID
JOIN IP_FLWSHT_MEAS IP_FLWSHT_MEAS2 ON ( (IP_FLWSHT_REC2.FSD_ID = IP_FLWSHT_MEAS2.FSD_ID) AND (IP_FLOWSHEET_ROWS2.LINE = IP_FLWSHT_MEAS2.OCCURANCE) )

WHERE IP_FLWSHT_MEAS2.FLO_MEAS_ID = '3040104029' AND IP_LDA_NOADDSINGLE2.IP_LDA_ID = IP_LDA_NOADDSINGLE.IP_LDA_ID
)
ELSE
IP_FLO_GP_DATA.DISP_NAME
END AS [LDA Group Name]

, DATEDIFF(ww,IP_LDA_NOADDSINGLE.PLACEMENT_INSTANT, IP_FLWSHT_MEAS.RECORDED_TIME ) AS [Weeks]
, DATEDIFF(dd, IP_LDA_NOADDSINGLE.PLACEMENT_INSTANT, IP_FLWSHT_MEAS.RECORDED_TIME ) AS [Days]



FROM IP_LDA_NOADDSINGLE
JOIN IP_FLOWSHEET_ROWS ON IP_LDA_NOADDSINGLE.IP_LDA_ID = IP_FLOWSHEET_ROWS.IP_LDA_ID
JOIN IP_FLWSHT_REC ON IP_FLOWSHEET_ROWS.INPATIENT_DATA_ID = IP_FLWSHT_REC.INPATIENT_DATA_ID
JOIN IP_FLWSHT_MEAS ON ( (IP_FLWSHT_REC.FSD_ID = IP_FLWSHT_MEAS.FSD_ID) AND (IP_FLOWSHEET_ROWS.LINE = IP_FLWSHT_MEAS.OCCURANCE) )
JOIN IP_FLO_GP_DATA ON IP_LDA_NOADDSINGLE.FLO_MEAS_ID = IP_FLO_GP_DATA.FLO_MEAS_ID
JOIN PAT_ENC_HSP ON IP_LDA_NOADDSINGLE.PAT_ENC_CSN_ID = PAT_ENC_HSP.PAT_ENC_CSN_ID
JOIN PAT_ENC ON PAT_ENC_HSP.PAT_ENC_CSN_ID = PAT_ENC.PAT_ENC_CSN_ID
LEFT OUTER JOIN CLARITY_SER ON PAT_ENC.VISIT_PROV_ID = CLARITY_SER.PROV_ID
LEFT OUTER JOIN CLARITY_SER ADMIT_SER ON PAT_ENC_HSP.BILL_ATTEND_PROV_ID = ADMIT_SER.PROV_ID
JOIN PATIENT_LIST ON IP_LDA_NOADDSINGLE.PAT_ID = PATIENT_LIST.PAT_ID
JOIN PATIENT ON IP_LDA_NOADDSINGLE.PAT_ID = PATIENT.PAT_ID

WHERE IP_FLWSHT_MEAS.FLO_MEAS_ID = '30410870'


)

--SELECT *
--FROM MAIN
--WHERE MAIN.ROW = 1
-- AND(
-- (MAIN.[HEALING PERCENTAGE] BETWEEN 0 AND @HEALING_PERCENTAGE AND MAIN.[WEEKS] > @WEEKS)
-- OR
-- (MAIN.[WEEKS] > @TOTAL_WEEKS AND MAIN.[HEALING PERCENTAGE] <> 100 AND MAIN.[HEALING PERCENTAGE] >= 0)
-- )
--ORDER BY [LDA Enc Provider], [Patient Name]

-- Include Negative Numbers

--SELECT *
--FROM MAIN
--WHERE MAIN.ROW = 1
-- AND(
-- (MAIN.[HEALING PERCENTAGE] <= @HEALING_PERCENTAGE AND MAIN.[WEEKS] > @WEEKS)
-- OR
-- (MAIN.[WEEKS] > @TOTAL_WEEKS AND MAIN.[HEALING PERCENTAGE] <> 100 )
-- )
--ORDER BY [LDA Enc Provider], [Patient Name]

SELECT *
, CASE WHEN (
(MAIN.[HEALING PERCENTAGE] <= @HEALING_PERCENTAGE AND MAIN.[WEEKS] > @WEEKS)
OR
(MAIN.[WEEKS] > @TOTAL_WEEKS AND MAIN.[HEALING PERCENTAGE] <> 100 )
) THEN
1
ELSE
0
END AS [Outlier]
, CASE WHEN MAIN.[LDA Group Name] IS NULL THEN
'Other Ulcers'
ELSE
MAIN.[LDA Group Name]
END [LDA Group]

FROM MAIN
WHERE MAIN.ROW = 1
AND MAIN.[LDA Healing Date] BETWEEN @StartDate AND @EndDate
AND [Healing Percentage] IS NOT NULL
AND [WEEKS] IS NOT NULL

ORDER BY
--[LDA Enc Provider],

[Patient Name]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers