I'm not sure I'm following. Do you just want the "last" status from the history table for each PK in the Master table? Or do you want the status from the Master table if it is less than or equal to the date variable and, if the Master table row is greater than that date, then get the last status from the History table? (I'm not even sure I follow myself.. )

Let me ask another way... Of the results you have shown above, which table does that result come from?

It's also a good idea to put your data in a consumable format. I formatted it for you so that others might be able to run queries against it:

I'm still not sure of the logic, but I was able to get the results you aked for. If that doesn't work for your full data set, let us know and we can refine the logic. This may not be the best solution, but I think it works:

SELECT
pk
,status
FROM
(
SELECT
Master.pk
,CASE WHEN Master.status_date <= '20121115' THEN Master.status ELSE History.status END AS Status
,ROW_NUMBER() OVER (PARTITION BY Master.pk ORDER BY CASE WHEN Master.status_date <= '20121115' THEN Master.status_date ELSE History.status_date END DESC) AS RowNum
FROM
@Master_table AS Master
FULL OUTER JOIN
@History AS History
ON Master.pk = History.pk_master_table
WHERE
Master.status_date <= '20121115'
OR (Master.status_date > '20121115' AND History.status_date <= '20121115')
) AS A
WHERE
RowNum = 1

Thanks Lamprey for formatting, I tried to keep the sample table in a tabbed format, but it did not work.

To answer your question, the result will come from master_table. The result will display just the PK and the amount. But selection of the PK is decided based on the input date (@date) to the query and the @date will be used to analyze in the status history table.

Basically all the records from master_table will be analyzed and classified into 4 categories: APPROVED, DENIED, IN PROGRESS, UNDER REVIEW

SELECT pk_master_table as pk, status
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY pk_master_table ORDER BY status_date DESC) AS RowNum
FROM @History
WHERE status_date <= '20121115'
) AS A
WHERE
RowNum = 1