Query for specific time range after the basis date

Question

I would like to query all patients that have a visit anything from 6 weeks to 6 months after the date of surgery and visit from 9 months to 15-18 months after the date of surgery.

I've used DateAdd function but it seems like I'm missing something.

SELECT distinct a.SUBJECTID
FROM [dbo].[XX] a
JOIN [dbo].[HH] b
on a.SUBJECTID = b.SUBJECTID
where b.HH_TOTAL_SCORE is not null 
and (b.HH_EVAL_DATE >= (DATEADD(WEEK,6,a.srg_date)) and b.HH_EVAL_DATE    <= (DATEADD(MONTH,6,A.SRG_DATE)))

Answer 1

I believe you need to join your HH table a second time to get only those people that have had an after surgery visit in both date ranges.

SELECT distinct a.SUBJECTID
FROM [dbo].[XX] a
JOIN [dbo].[HH] b on a.SUBJECTID = b.SUBJECTID
JOIN [dbo].[HH] c on a.SUBJECTID = c.SUBJECTID
where b.HH_TOTAL_SCORE is not null 
and (b.HH_EVAL_DATE >= (DATEADD(WEEK,6,a.srg_date)) and b.HH_EVAL_DATE    <= (DATEADD(MONTH,6,A.SRG_DATE)))
and (c.HH_EVAL_DATE >= (DATEADD(MONTH,9,a.srg_date)) and c.HH_EVAL_DATE    <= (DATEADD(MONTH,18,A.SRG_DATE)))

Note that I am interpreting your statement "visit from 9 months to 15-18 months after the date of surgery" to simply mean a visit from 9 to 18 months after the date of surgery.