ChrisM@Work (8/12/2013)
I'd focus on the one table for now, which gives you this query:
SELECT
jt.trans_num,
emp_num = CASE
WHEN jt.emp_num IS NOT NULL
THEN jt.emp_num
ELSE
(SELECT TOP 1
jtx.emp_num
FROM jobtran jtx(NOLOCK)
WHERE jtx.job = jt.job
ANDjtx.suffix = jt.suffix
AND jtx.oper_num >= jt.oper_num
ANDjtx.emp_num IS NOT NULL
AND jtx.trans_num < jt.trans_num
ORDER BY jtx.oper_num, jtx.trans_num DESC)
END
FROM jobtran jt (NOLOCK)
LEFT JOIN (
SELECT
trans_num = MIN(trans_num), -- Set up time only backflushed for first booking,
job,-- so check that no previous booking exists for op.
suffix,
oper_num
FROM jobtran (NOLOCK)
GROUP BY job, suffix, oper_num
) jt2
ON jt2.job = jt.job
AND jt2.suffix = jt.suffix
AND jt2.oper_num = jt.oper_num
AND jt2.trans_num < jt.trans_num
WHERE jt.trans_class = 'J'
ANDISNULL(jt.posted,0) = 1-- Only show posted transactions
Why did you include the LEFT JOIN in this statement? It doesn't appear to be used anywhere.
Other than that the code is the same I posted in my original post.
Just curious as to how to diagnose these things for myself in the future.