SQLTeam.com | Weblogs | Forums

Split 2 years into a month to month comparison


#1

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