• 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