May 11, 2015 at 11:56 am
Theleft outer joins are causing the query to retrieve more data... can someone help or suggest rewriting the query ?
SELECT
ls.accid,
ls.TerminationDate,
MAX(P.lchg),
MAX(PP.lchg),
MAX(TL.lchg),
MAX(TLS.lchg),
MAX(T.lchg),
MAX(TRL.lchg),
MAX(PI.lchg),
MIN(PF.lchg)
FROM
#LS ls
LEFT OUTER JOIN #pos P ON ls.accid = p.accid AND ls.bid = P.bid AND ls.oid = P.oid AND P.inum = @inum_5
LEFT OUTER JOIN #pos PP ON ls.accid = PP.accid AND ls.bid = PP.bid AND ls.oid = PP.oid AND PP.inum = @inum_8
LEFT OUTER JOIN #TL TL ON ls.accid = TL.accid AND ls.bid = TL.bid AND ls.oid = TL.oid AND TL.inum = @inum_5
LEFT OUTER JOIN #TL TLS ON ls.accid = TLS.accid AND ls.bid = TLS.bid AND ls.oid = TLS.oid AND TLS.inum = @inum_8
LEFT OUTER JOIN #Txn T ON ls.accid = T.accid AND ls.bid = T.bid AND ls.oid = T.oid AND T.inum = @inum_5
LEFT OUTER JOIN #Txn TRL ON ls.accid = TRL.accid AND ls.bid = TRL.bid AND ls.oid = TRL.oid AND TRL.inum = @inum_9
LEFT OUTER JOIN #Txn PI ON ls.accid = PI.accid AND ls.bid = PI.bid AND ls.oid = PI.oid AND PI.inum = @inum_7 AND PI.fld4_tms = DATEADD(YEAR, 1, @EndDate)
LEFT OUTER JOIN #Prfmce PF ON ls.accid = PF.accid
GROUP BY ls.accid,TerminationDate
ORDER BY ls.accid;
May 11, 2015 at 12:55 pm
Please provide DDL, sample data and expected results based on that sample data.
Check the following article to help you with the correct way to do it: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
It's not an unbreakable rule to post this, but this way you'll get tested solutions (which will work better and will be delivered faster).
May 11, 2015 at 2:53 pm
do you have indexes on position, tax lots and transactions temp tables. try to use windows function if you are on SQL2014/12.
May 11, 2015 at 3:35 pm
Just so you know, one of the purposes of a left join is to return results (NULLS) when there is no corresponding data in the joined table. Sounds like you may need to combine INNER JOINS with LEFT OUTER JOINS. But as stated earlier, we need a lot more detail.
May 11, 2015 at 4:51 pm
I have used CTE to overcome the issue
May 12, 2015 at 2:50 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply