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