Sean Lange (3/20/2013)
I think that the above would miss Claim 7149 which as I read your issue should show up on the list. This is easy enough to correct by changing @StatusDate to a datetime and the where clause to StatusDate <= @StatusDate
Actually, Sean, my code works as is. If you change the value in @StatusCode from 300 to 210 it catches Claim 7149 just fine.
If you look closely, I cast the Datetime column to a date value (this is SQL Server 2008) and do a <+. Else wise you need to add 1 day to the date you are using, for 2013-03-14 you would want < '2013-03-15' if looking at the full datetime value in StatusDate.