ChrisM@Work (8/12/2013)
This, I think, would make a reasonable test query:
SELECTjt.job,jt.suffix, jt.oper_num, jt.trans_num, jt.emp_num,
x.oper_num, x.trans_num, x.emp_num,
jt2.trans_num
FROM jobtran jt (NOLOCK)
OUTER APPLY (
SELECT TOP 1
jtx.emp_num, jtx.oper_num, jtx.trans_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
) x
OUTER APPLY ( -- Set up time only backflushed for first booking,
SELECT -- so check that no previous booking exists for op.
trans_num = MIN(trans_num)
FROM jobtran j (NOLOCK)
WHERE j.job = jt.job
AND j.suffix = jt.suffix
AND j.oper_num = jt.oper_num
) jt2
WHERE jt.trans_class = 'J'
ANDISNULL(jt.posted,0) = 1 -- Only show posted transactions
So what you're doing here is testing for the result of different scenarios? I like the way I can see how the emp_num can be traced through the different scenarios.
I'd tried using OUTER APPLY before posting and found that (with my statement) that there was no performance gain.