I have a query that returns a list of patients including a discharge date, admit date, account number and drg. It currently lists all patients. The unique identifier to the patient is the unit number. The account number is different every time they come in. I need to know for each patient - if the admitdate is within 30 days of the last disdate and only if the drg was the same for both account numbers. The query below is very basic - only showing the fields and their tables. I have no idea how to do this.... I've attached some sample data and shaded in the ones that would need to be returned from the query.
Basically we are trying to find out which patients came back within 30 days for the same thing.
I am writing this report in Visual Studio with SQL Server 2005
SELECT AbsPatDischarges.DisDate, AbsPat.UnitNumber, AbsPat.AccountNumber, AbsPatDrgData.Drg, AbsPat.PtStatus, AdmPatientFile.AdmitDate
FROM AbsPat INNER JOIN
AbsPatDischarges ON AbsPat.UrnABSPAT = AbsPatDischarges.UrnABSPAT INNER JOIN
AbsPatDrgData ON AbsPatDischarges.UrnABSPAT = AbsPatDrgData.UrnABSPAT INNER JOIN
AdmPatientFile ON AdmPatientFile.UrnADMPAT = AbsPat.AdmUrn
WHERE (AbsPat.PtStatus LIKE 'in')
ORDER BY AbsPat.UnitNumber, AbsPatDischarges.DisDate