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:DECLARE @Master_table TABLE (pk int, status_date date, status VARCHAR(50), amount INT)INSERT @Master_table VALUES(1, '1/1/13', 'approved', 100),(2, '12/1/12', 'denied', 0),(3, '11/1/11', 'in progress', 200),(4, '10/1/12', 'approved', 300)DECLARE @History TABLE (pk int, pk_master_table int, status_date date, status VARCHAR(50))INSERT @History VALUES(1, 1, '11/30/12', 'in progress'),(3, 1, '12/10/2', 'under review'),(5, 1, '1/1/13', 'approved'),(2, 2, '10/1/12', 'in progress'),(4, 2, '11/1/12', 'under review'),(6, 2, '12/1/12', 'denied'),(7, 3, '11/1/11', 'in progress'),(8, 4, '9/1/12', 'in progress'),(9, 4, '9/15/12', 'under review'),(10, 4, '10/1/12', 'approved')