Good afternoon,
What I am trying to accomplish is to compare 2 fiscal years 2014 and 2015
and to display a side to side(month to month) comparison.
I could code it using each month as a range (in the code) but there must be a more efficient way to code this
Fiscal year begin 7-01 and end on 6-30
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
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 PD
WHERE PD.NAME_LAST IS NOT NULL
AND PD.NAME_LAST IS NOT NULL
AND PD.STATUS_CODE LIKE 'P02'
UNION
SELECT
RB.ORGANIZATION_NAME, PD.STATUS_CODE, PD.PATIENT_CODE, PD.PROSPECT_ADMIT_DATE
FROM PT_Prospect_Date 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 PD
WHERE PD.STATUS_CODE LIKE 'P01'
) X INNER JOIN PT_Prospect_Date PD ON X.PATIENT_CODE = PD.PATIENT_CODE
WHERE
PD.PROSPECT_ADMIT_DATE >= '20130701' AND PD.PROSPECT_ADMIT_DATE <= '20160630'
AND X.STATUS_CODE LIKE 'P%'
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 feedback on how to optimize this code would be great!
Thanks again everyone