CASE doesn't work that way in SQL.

CASE in SQL returns a value based on conditions. You're using it more like a VB version of CASE, where it executes code.

Convert to a format like this:

WHERE lr.sample_date >= CASE

WHEN datepart(month,pat.start) = datepart(month, getdate()) - 1

THEN dateadd(m, datediff(m, 0, GETDATE()) - 6, 0)

And so on, with each pat.start rule after a WHEN statement, and each date range after the corresponding THEN statement.

Make sense?

Also, you're testing that "datepart(month) = datepart(month)", which won't take year into effect. This may or may not matter in your data, but it would in most databases. The way you're testing it, if a patient's start date was October last year, it would count as "last month", because datepart(month, 'October last year') is 10, and that's 1 less than 11 (datepart(month, 'November this year')). (Please ignore my syntax errors on datepart. I'm making a point, not writing code.)

You'll be better off using date ranges, and dateadd than datepart, for that kind of thing. Unless your data somehow prevents last year from ever mattering here.