Luis Cazares (7/30/2015)
What are you expecting to accomplish with the subquery? It basically compares one column to itself joined by the same condition.
Looks like an expensive attempt to get the most resent row from tblJ for each BID... The correlated sub-query version of the following...
;WITH J AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY BID ORDER BY dt DESC) AS RN
FROM tblJ
)
SELECT
*
FROM
tblo o
LEFT JOIN tblb b ON o.binid = b.binid
LEFT JOIN tblp p ON p.pid = o.pid
LEFT JOIN tblof o1 ON o1.pid = p.pid
LEFT JOIN J ON b.BID = J.BID AND J.RN = 1
WHERE
o.C LIKE @C