Below is my SQL so far:
R.Account as RAccount,
R.AdmitDate as RAdmitDate,
R.[Discharge Date] as RDischargeDate,
DATEDIFF(day, D.[Discharge Date], R.ADMITDATE) as DaysSince,
R.AdmitDX AS RAdmitDx,--#4
Dx.Description as RAdmitDxDesc --#4
Encounter_ALL_ADM D WITH (NOLOCK)
LEFT OUTER JOIN Encounter_ALL_ADM R WITH (NOLOCK)
ON D.MRN = R.MRN --AND
AND D.Account <> R.Account
--ON D.EID = R.EID
--AND D.Account = R.Account
--AND D.Institution = 'SMMC'
AND R.PatientType = 'IN'
AND (R.ADMITDATETIME - D.DISCHARGEDATETIME) BETWEEN 0 and 30
LEFT OUTER JOIN Dictionary_Diagnosis_Code Dx --#4
ON Dx.Code = R.AdmitDX --#4
--AND Dx.Code = D.AdmitDX
AND Dx. [EFFECTIVE_DATE] >= '01-OCT-2013'
D.AccountStatus_PatientType IN ('DIS_IN')
AND D.DISCHARGEDATETIME between DATEADD(mm, -12, GetDate())and DATEADD(mm, 0, GetDate())
AND D.PatientType = 'IN'
Question - Where it says 'Dx. [EFFECTIVE_DATE] >= '01-OCT-2013'' - I need to pull the data as of October 1 of previous year. No matter when the report is run