asked on

SQL Server - How to pull data for October of Previous Year

Below is my SQL so far:

SELECT DISTINCT
D.Account,
R.Account as RAccount,
D.MRN,
D.AdmitDate,
D.[Discharge Date],
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
FROM
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'

WHERE
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

Scott Pletcher

AND Dx. [EFFECTIVE_DATE] >= DATEADD(MONTH, 9, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))

Btw:
AND D.DISCHARGEDATETIME >= DATEADD(mm, -12, GetDate()) AND D.DISCHARGEDATETIME < DATEADD(mm, 0, GetDate())
*

rustypoot

ASKER

Thanks Scott! Month should be 10 right?

Dx. [EFFECTIVE_DATE] >= DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))

Not sure what you mean for the SQL below!

Btw:
AND D.DISCHARGEDATETIME >= DATEADD(mm, -12, GetDate()) AND D.DISCHARGEDATETIME < DATEADD(mm, 0, GetDate())

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial