• You might want to consider adding a flitered index on deceased date including the PatientNumber and DivisionCode. Then you can do an easy self-join, which I think should be efficient as well.

    SELECT d.DCDate, d.PatientNumber, p.* --,...

    FROM [PatientData] d

    INNER JOIN [PatientData] p ON p.PatientNumber = d.PatientNumber AND p.VisitDate BETWEEN DATEADD(YEAR, -1, d.DCDate) AND d.DCDate

    WHERE d.DCDate IS NOT NULL AND d.DivisionCode IN ('A', 'B', 'C')

    ORDER BY d.DCDate

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.