Hello everyone,
I have this query everything is working fine but PD.STATUS_CODE 'HL1' will not appear in query
I am soo confused
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