Something like this:

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;