Status code not populating in query

Hello everyone,

I have this query everything is working fine but PD.STATUS_CODE 'HL1' will not appear in query
I am soo confused :frowning:

SELECT x.ACCOUNT_NAME, X.STATUS_CODE,
SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014 THEN 1 ELSE 0 END) AS FY2014,
SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015 THEN 1 ELSE 0 END) AS FY2015,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2013-07-01',120) AND CONVERT(DATETIME,'2013-07-31',120) then 1 else 0 end) AS JulFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-07-01',120) AND CONVERT(DATETIME,'2014-07-31',120) then 1 else 0 end) AS JulFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2013-08-01',120) AND CONVERT(DATETIME,'2013-08-31',120) then 1 else 0 end) AS AugFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-08-01',120) AND CONVERT(DATETIME,'2014-08-31',120) then 1 else 0 end) AS AugFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2013-09-01',120) AND CONVERT(DATETIME,'2013-09-30',120) then 1 else 0 end) AS SepFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-09-01',120) AND CONVERT(DATETIME,'2014-09-30',120) then 1 else 0 end) AS SepFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2013-10-01',120) AND CONVERT(DATETIME,'2013-10-31',120) then 1 else 0 end) AS OctFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-10-01',120) AND CONVERT(DATETIME,'2014-10-31',120) then 1 else 0 end) AS OctFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2013-11-01',120) AND CONVERT(DATETIME,'2013-11-30',120) then 1 else 0 end) AS NovFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-11-01',120) AND CONVERT(DATETIME,'2014-11-30',120) then 1 else 0 end) AS NovFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2013-12-01',120) AND CONVERT(DATETIME,'2013-12-31',120) then 1 else 0 end) AS DecFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-12-01',120) AND CONVERT(DATETIME,'2014-12-31',120) then 1 else 0 end) AS DecFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-01-01',120) AND CONVERT(DATETIME,'2014-01-31',120) then 1 else 0 end) AS JanFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2015-01-01',120) AND CONVERT(DATETIME,'2015-01-31',120) then 1 else 0 end) AS JanFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-02-01',120) AND CONVERT(DATETIME,'2014-02-28',120) then 1 else 0 end) AS FebFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2015-02-01',120) AND CONVERT(DATETIME,'2015-02-28',120) then 1 else 0 end) AS FebFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-03-01',120) AND CONVERT(DATETIME,'2014-03-31',120) then 1 else 0 end) AS MarFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2015-03-01',120) AND CONVERT(DATETIME,'2015-03-31',120) then 1 else 0 end) AS MarFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-04-01',120) AND CONVERT(DATETIME,'2014-04-30',120) then 1 else 0 end) AS AprFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2015-04-01',120) AND CONVERT(DATETIME,'2015-04-30',120) then 1 else 0 end) AS AprFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-05-01',120) AND CONVERT(DATETIME,'2014-05-31',120) then 1 else 0 end) AS MayFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2015-05-01',120) AND CONVERT(DATETIME,'2015-05-31',120) then 1 else 0 end) AS MayFy15,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-06-01',120) AND CONVERT(DATETIME,'2014-06-30',120) then 1 else 0 end) AS JunFy14,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2015-06-01',120) AND CONVERT(DATETIME,'2015-06-30',120) then 1 else 0 end) AS JunFy15

FROM

(SELECT
PD.NAME_LAST + ' ' + PD.NAME_FIRST AS ACCOUNT_NAME, PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE PD.NAME_LAST IS NOT NULL
AND PD.NAME_LAST IS NOT NULL
AND PD.STATUS_CODE LIKE 'P02'

UNION
SELECT
PD.NAME_FULL, PD.STATUS_CODE, PD.PATIENT_CODE, PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE PD.STATUS_CODE LIKE 'P06' OR PD.STATUS_CODE LIKE 'P08'

UNION
SELECT
PD.NAME_FULL, PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE
PD.STATUS_CODE LIKE 'HL1'

UNION
SELECT
RB.ORGANIZATION_NAME, PD.STATUS_CODE, PD.PATIENT_CODE, PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD INNER JOIN
RES_BASIC RB ON RB.RESOURCE_ID = PD.ASSOCIATED_FACILITY_ID
WHERE RB.ORGANIZATION_NAME IS NOT NULL
AND PD.STATUS_CODE LIKE 'P%'

UNION
SELECT
CASE WHEN PD.NAME_FULL IS NOT NULL THEN 'Consumer' END , PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE PD.STATUS_CODE LIKE 'P01'

) X INNER JOIN PT_Prospect_Date PD ON X.PROSPECT_ADMIT_DATE = PD.PROSPECT_ADMIT_DATE AND X.PATIENT_CODE = PD.PATIENT_CODE

WHERE

PD.PROSPECT_ADMIT_DATE >= '20130701' AND PD.PROSPECT_ADMIT_DATE <= '20160630'
--AND X.STATUS_CODE LIKE 'P%' OR X.STATUS_CODE LIKE 'HL1'

GROUP BY
X.STATUS_CODE,
X.ACCOUNT_NAME

HAVING NOT ( SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014 THEN 1 ELSE 0 END) = 0 AND
SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015 THEN 1 ELSE 0 END) = 0 )

ORDER BY
X.STATUS_CODE

I highlighted the section of code that isn't working for me
any advice would be appreciated

Thanks,
M

You have to debug it in steps.

First, run the inner query and see if that returns any rows

SELECT
PD.NAME_FULL, PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD 
WHERE 
PD.STATUS_CODE = 'HL1'

If it does, mak ea not of some of the PD.PROSPECT_ADMIT_DATE values

Then run the entire query, but after removing the HAVING clause. Does it return any rows? If it does then the having clause is the reason the data gets eliminated.

If it still does not return any data, check the dates in PT_Prospect_Date table and compare them with the data in PT_Prospect_Date_2 table. Are they of the same data type? Are there rows that would match - i.e., have the same dates?

1 Like

Thanks for your input James

I made a noob error :frowning: i feel dumb...

X INNER JOIN PT_Prospect_Date_2 PD ON X.PROSPECT_ADMIT_DATE = PD.PROSPECT_ADMIT_DATE AND X.PATIENT_CODE = PD.PATIENT_CODE

fixed it I'm sorry :frowning: