status on a given date or date range

CREATE TABLE ClaimStatus(
ClaimID int NOT NULL,
StatusDate datetime NOT NULL,
StatusCode int NOT NULL);

INSERT INTO ClaimStatus VALUES (7150,'2013-03-11 10:41:29.823',100);
INSERT INTO ClaimStatus VALUES (7150,'2013-03-12 07:20:41.720',300);
INSERT INTO ClaimStatus VALUES (7150,'2013-03-15 13:35:50.000',310);
INSERT INTO ClaimStatus VALUES (7148,'2013-03-01 10:41:29.780',100);
INSERT INTO ClaimStatus VALUES (7148,'2013-03-10 07:21:26.557',300);
INSERT INTO ClaimStatus VALUES (7148,'2013-03-20 13:35:50.000',310);
INSERT INTO ClaimStatus VALUES (7149,'2013-02-01 01:19:20.110',100);
INSERT INTO ClaimStatus VALUES (7149,'2013-02-14 07:21:26.557',300);
INSERT INTO ClaimStatus VALUES (7149,'2013-03-14 00:35:50.000',310);
INSERT INTO ClaimStatus VALUES (7147,'2013-02-01 01:19:20.110',100);
INSERT INTO ClaimStatus VALUES (7147,'2013-02-14 07:21:26.557',300);
INSERT INTO ClaimStatus VALUES (7147,'2013-03-10 00:35:50.000',310);

declare @StatusDate date = '20130314',
@StatusCode int = 300;

with BaseData as (
select
ClaimID,
StatusDate,
StatusCode,
rn = row_number() over (partition by ClaimID order by StatusDate desc)
from
ClaimStatus
where
cast(StatusDate as date) <= @StatusDate
)
select * from BaseData where rn = 1 and StatusCode = @StatusCode;

drop table ClaimStatus;

Lynn Pettis (3/21/2013)

Sean Lange (3/21/2013)

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 <= @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.

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.

Based on the OP's original post the status changed on that date from 300 to 310, so this record would not be shown. His requirement simply specified that on a given date was a claim still in a given state, not if it had been in that state. The claim changed state on that date.

Let's say I need a query that would tell me which claims were at a status of 300 on 3/14/2013. Some of these claims were, in fact, at a status of 300 on that day but how do I query that since there is no entry for the specific date of 3/14/2013? ( ie: claim 7148 was changed to a status of 300 on 3/10 and was not changed to another status until 3/20, so I would need that claim pulled back in my query as it would have still been status 300 on 3/14.)

Nothing above indicated that he wanted to see those claims that changed status on that date from 300, but that were still in a status of 300 on a given date and had not changed yet. Nothing indicates that time is relevant to the query.

Trust me, if time had been relevant to the query I would have accounted for it.