calculating percentage - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

calculating percentage

Author

Topic

tjonas
Starting Member

17 Posts

Posted - 2015-03-05 : 15:58:09

I'm trying to calculate the % of people but it doesn't seem to be working. I get the following error:
ORA-00904: "ACT"."PAT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"

What am I doing wrong?

WITH ENCOUNTERS AS
(
SELECT COUNT(*) AS TOT_COUNT
FROM
(
SELECT DISTINCT PATIENT.PAT_ID
FROM PAT_ENC
INNER JOIN PATIENT ON PAT_ENC.PAT_ID = PATIENT.PAT_ID
WHERE PAT_ENC.DEPARTMENT_ID IN (1043105,1043114, 1043107)
AND PAT_ENC.ENC_TYPE_C in ('50','101','200','227','280','300','334','335','369','5376','701','702','703','706','708')
AND TRUNC((TRUNC (SYSDATE) - COALESCE(PATIENT.BIRTH_DATE, SYSDATE)) / 365.25) >= 4
AND PAT_ENC.CONTACT_DATE >= TO_DATE('02/01/2014','MM/DD/YYYY')
)
),
ACT AS
(
SELECT COUNT(*) AS ACT_COUNT
FROM
(
SELECT DISTINCT PATIENT.PAT_ID, disp_name
FROM PAT_ENC
INNER JOIN PATIENT ON PAT_ENC.PAT_ID = PATIENT.PAT_ID
LEFT JOIN IP_FLWSHT_REC ON PAT_ENC.INPATIENT_DATA_ID = IP_FLWSHT_REC.INPATIENT_DATA_ID
LEFT JOIN IP_FLWSHT_MEAS ON IP_FLWSHT_REC.FSD_ID = IP_FLWSHT_MEAS.FSD_ID
LEFT JOIN IP_FLO_GP_DATA ON IP_FLO_GP_DATA.FLO_MEAS_ID = IP_FLWSHT_MEAS.FLO_MEAS_ID
WHERE PAT_ENC.DEPARTMENT_ID IN (1043105,1043114, 1043107)
AND IP_FLWSHT_MEAS.FLT_ID in ('501','502')
AND PAT_ENC.ENC_TYPE_C in ('50','101','200','227','280','300','334','335','369','5376','701','702','703','706','708')
AND TRUNC((TRUNC (SYSDATE) - COALESCE(PATIENT.BIRTH_DATE, SYSDATE)) / 365.25) >= 4
AND PAT_ENC.CONTACT_DATE >= TO_DATE('02/01/2014','MM/DD/YYYY')
)
)
SELECT ((ENCOUNTERS.TOT_COUNT/ACT.ACT_COUNT)*100)
FROM ENCOUNTERS
INNER JOIN ACT ON ENCOUNTERS.PAT_ID = ACT.PAT_ID

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-03-05 : 18:19:43

This forum is intended for mssql, however you are joining on pat_id, but only return count

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-10 : 03:47:12

You need to add PAT_ID in both the CTEs

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources