Hi everyone,
I know this was a question I asked before but after re verifying the results
My union for p02 is displaying 14 values when it should be showing 23...
example I could run
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.STATUS_CODE LIKE 'P02'
AND PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-08-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120)
and it will display 23 values... but results differ from the union...
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, '2014-08-01', 120) AND CONVERT(DATETIME, '2014-08-31', 120) THEN 1 ELSE 0 END)AS FY2015MONTH,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-08-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120) THEN 1 ELSE 0 END) AS FY2016MONTH,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2014-07-01', 120) AND CONVERT(DATETIME, '2014-08-31', 120) THEN 1 ELSE 0 END) AS FY2015YTD,
SUM(CASE WHEN PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME, '2015-07-01', 120) AND CONVERT(DATETIME, '2015-08-31', 120) THEN 1 ELSE 0 END) AS FY2016YTD
FROM
(SELECT
PD.NAME_FULL AS ACCOUNT_NAME, PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE
PD.STATUS_CODE LIKE 'HL1'
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 ALL -- union all is not helping me retrieve all values...
SELECT
PD.NAME_LAST + ' ' + PD.NAME_FIRST, PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date_2 PD
WHERE
PD.STATUS_CODE LIKE 'P02'
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_2 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'
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
any help would be greatly appreciated
stressful problem
Thanks in advance!
Matt