Lynn Pettis (3/20/2013)
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 <= @StatusDateActually, 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.
Lynn, claim 7149 was in a status of 300 until 00:35:50.000 on 2013-03-14 and the code you posted does not return it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/