Trying to a Restrict Query by Max Status on Max Date

Transact-SQL
1

I already have most of the query I am working with correct

Select Clients.rid, Clients.r_fname, Clients.r_lname, Status.Status
From Clients Inner Join
  Status On Clients.rid = Status.rid
Where (Status.Status In (12, 19) Or Status.Status = 29) And Status.wave = 9 And
  Status.StatusDate = (Select Max(Status.StatusDate) From Status
  Where Clients.rid = Status.rid And)

I only want results to turn up in my query for those with status 29, 19, or 12 on the most recent date, and not those with a higher value more recently i.e. 40, 42 etc. My issue is that when 40 and and 29 for example happen on the same date the person comes up in my query, even though I am only interested in that person if their most recent value is 40. So I would like to select a person in the query if their status is 29 on the most recent date, but only if 12 or 19 or 29 is their max status, not if the max status is anything higher than that. I tried this but I'm not sure of the syntax, I've been getting an error message.

Select Clients.rid, Clients.r_fname, Clients.r_lname, Status.Status
From Clients Inner Join
  Status On Clients.rid = Status.rid
Where (Status.Status In (12, 19) Or Status.Status = 29) And Status.wave = 9 And
  Status.StatusDate = (Select Max(Status.StatusDate) From Status
  Where Clients.rid = Status.rid And Max(Status.Status) = 29 or 19 or 12)

Any help would be appreciated, thanks!

2

Not 100% sure where you need to test for the value of "wave", but something like this should do it:

Select Clients.rid, Clients.r_fname, Clients.r_lname, Status.Status
From Clients 
Inner Join (
    Select rid, Max(StatusDate) As StatusDate
    From Status
    Group By rid
    Having Max(Status) <= 29
) As Status_Max On Clients.rid = Status_Max.rid And Status_Max.Status In (12, 19, 29)
Inner Join 
  Status On Status.rid = Status_Max.rid And Status.StatusDate = Status_Max.StatusDate 
      And Status_Max.wave = 9
1 Like
3

Thanks for responding! This seems like it should work but I keep getting the error message now

Invalid column name 'Status'. Invalid column name 'Status'. Invalid column name 'Status'.

1112×660 48.1 KB

This was the information displayed in my original query

rid r_fname r_lname Status

Thanks!!

4

This was very helpful thanks!! Pretty much worked, I just tweaked a couple little things but that's only because I was really vague to begin with .