I believe the following will work in SQL2008. I don't have a SQL2008 test environment handy to test.

;

WITH pd AS (

SELECT

pd.PatientNumber,

pd.DCDispCode,

pd.DCDate,

MAX(CASE WHEN pd.DCDispCode = '20' THEN pd.DCDate END) OVER(PARTITION BY pd.PatientNumber) AS DeceasedDate

FROM PatientData pd

WHERE DivisionCode IN ('A', 'B', 'C')

)

SELECT

PatientNumber,

MAX(DeceasedDate) AS DeceasedDate,

COUNT(PatientNumber) AS PatientVisits

FROM pd

WHERE DCDate BETWEEN DATEADD(YEAR, -1, DeceasedDate) AND pd.DeceasedDate

GROUP BY PatientNumber

I think that this might perform better than the self-join.

Drew