• I think the reply from frfernan is your best bet. To add to that:

    You need not code an exact date, You can do like

    SELECT lr.*

    FROM lab_results lr

    ,patient pat

    WHERE lr.patient_id = pat.patient_id and lr.sample_date >=

    case datediff(month,pat.start_Date, getdate())

    when 0 then lr.sample_date +1

    when 1 then dateadd(month,datediff(month,0,getdate())-6,0) --- or dateadd(month, -6, getdate()) ...based on your exact requirement

    else DATEADD(month, DATEDIFF(month, 0, pat.start), 0)

    END

    Plus it is more readable and you dont have to worry about patients with tests going back years or when January is the current month like you do with your initial approach.

    Mark

    ----------------------------------------------------