I'm confused by your expected results.

You said you want to get the "latest status as of April 1, 2012." And state that you would expect ConsumerID 101 to be returned as "ACTIVE." Yet the dates in your sample data indicate that ConsumerID 101 is first inserted on January 10th, 2012 with an "INACTIVE" status, and the new status of "ACTIVE" is not inserted until May 20, 2012 (after your "as-of" report date). So I would have thought you would have expected ConsumerID 101 to be returned as "INACTIVE" based on your requirements.

Can you clarify on this point?