Comparing two dates in different tables SQL with criteria

Question

Click image to see the table structure and problm https://i.sstatic.net/odWKL.png

SELECT B.ENTITYID, 
       B.BALANCEDATE,
       B.BALANCE, 
       MIN(DATEDIFF(DAY,B.BALANCEDATE,C.STATUSDATE)) RECENT
FROM   BALANCES B JOIN STATUS C ON B.ENTITYID = C.ENTITYID
GROUP  BY B.ENTITYID, B.BALANCEDATE,B.BALANCE
HAVING B.ENTITYID =1

I have tried the following but can't go further than that as more nested selects have problems with accessing similar attributes:

Answer 1

Also in SQLServer2005+ you can use option with APPLY() operator.

The APPLY operator allows you to join two table expressions. Right table expression is processed every time for each row from the left table expression.The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression. OUTER APLLY for those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression.

SELECT e.EntityName, b.BalanceDate AS Date, b.Balance, o.Status
FROM Entity e JOIN Balances b ON e.EntityID = b.EntityID
              OUTER APPLY (
                           SELECT TOP 1 s.Status AS Status                           
                           FROM Status s
                           WHERE b.EntityID = s.EntityID 
                             AND s.StatusDate < b.BalanceDate
                           ORDER BY s.StatusDate DESC
                           ) o
WHERE e.EntityName = 'ABCD' 

For improving performance(force INDEX SEEK operation) use this indexes with INCLUDE clause.The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it's not part of the tree

CREATE INDEX x ON Status(StatusDate) INCLUDE(EntityID, Status)
CREATE INDEX x ON Entity(EntityName) INCLUDE(EntityID)
CREATE INDEX x ON Balances(EntityID, BalanceDate, Balance)

Demo on SQLFiddle