AK1516 (5/11/2015)
I have used CTE to overcome the issue
You could post your solution then: it would benefit future readers that run into the same problem.
A possible solution is to get rid of redundant joins and use a crosstab:
SELECT
ls.accid,
ls.TerminationDate,
MAX(CASE P.inum WHEN @inum_5 THEN P.lchg END),
MAX(CASE P.inum WHEN @inum_8 THEN P.lchg END),
MAX(CASE TL.inum WHEN @inum_5 THEN TL.lchg END),
MAX(CASE TL.inum WHEN @inum_8 THEN TL.lchg END),
MAX(CASE T.inum WHEN @inum_5 THEN T.lchg END),
MAX(CASE T.inum WHEN @inum_9 THEN T.lchg END),
MAX(CASE T.inum WHEN @inum_7 THEN T.lchg END),
MIN(PF.lchg)
FROM
#LS AS ls
LEFT OUTER JOIN #pos AS P
ON ls.accid = p.accid
AND ls.bid = P.bid
AND ls.oid = P.oid
AND P.inum IN (@inum_5,@inum_8)
LEFT OUTER JOIN #TL AS TL
ON ls.accid = TL.accid
AND ls.bid = TL.bid
AND ls.oid = TL.oid
AND TL.inum IN (@inum_5,@inum_8)
LEFT OUTER JOIN #Txn AS T
ON ls.accid = T.accid
AND ls.bid = T.bid
AND ls.oid = T.oid
AND T.inum IN (@inum_5,@inum_9,@inum_7)
AND CASE
WHEN T.inum = @inum7 AND T.fld4_tms = DATEADD(YEAR, 1, @EndDate) THEN 1
WHEN T.inum IN (@inum_5, @inum_9) THEN 1
ELSE 0
END
LEFT OUTER JOIN #Prfmce PF
ON ls.accid = PF.accid
GROUP BY ls.accid,ls.TerminationDate
ORDER BY ls.accid;
-- Gianluca Sartori