Here's the full statement:
SELECTjt.trans_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)
ENDAS 'emp_num',
CASE
WHENjt.trans_type = 'M' AND js3.sched_drv = 'L'-- M = Move
THENjt.a_hrs
ELSE0
ENDAS 'rework_hrs',
CASE
WHEN jt.trans_type IN ('R','C') AND js3.sched_drv = 'L'-- R = Run, C = Machine
THEN jt.a_hrs
ELSE 0
ENDAS 'run_hrs',
CASE
WHEN jt.trans_type = 'S' AND js3.sched_drv = 'L'-- S = Setup
THEN jt.a_hrs
ELSE 0
ENDAS 'setup_hrs',
CASE -- All std time captured on Move*, as this is where quantity is recorded
-- where Move trans types don't exist, qty is recorded on other trans types (e.g C)
WHEN ISNULL(jt.qty_moved,0) <> 0
AND jt2.trans_num IS NULL
THEN CASE
WHENi.cost_type = 'S'-- Standard Costed
THEN CASE
WHEN js.sched_drv = 'L' THEN js.setup_hrs + (js.run_mch_hrs * jt.qty_moved) + (js.run_lbr_hrs * jt.qty_moved)
ELSE 0
END
ELSECASE-- Actual Costed
WHEN js2.sched_drv = 'L' THEN js2.setup_hrs + (js2.run_mch_hrs * jt.qty_moved) + (js2.run_lbr_hrs * jt.qty_moved)
ELSE 0
END
END
WHEN ISNULL(jt.qty_moved,0) <> 0
THEN CASE
WHENi.cost_type = 'S'-- Standard Costed
THEN CASE
WHEN js.sched_drv = 'L' THEN(js.run_mch_hrs * jt.qty_moved) + (js.run_lbr_hrs * jt.qty_moved)
ELSE 0
END
ELSECASE-- Actual Costed
WHEN js2.sched_drv = 'L' THEN (js2.run_mch_hrs * jt.qty_moved) + (js2.run_lbr_hrs * jt.qty_moved)-- Actual Costed
ELSE 0
END
END
ELSE 0
END AS 'std_hours',
trans_date,
jt.wc,
i.Uf_renItemProjectAS 'project_no',
ISNULL(wc.Uf_renWCSalvage,0)AS 'salvage_wc',
jt.CreateDate
FROMjobtran jt (NOLOCK)
JOINjob j (NOLOCK)
ONj.job = jt.job
ANDj.suffix = jt.suffix
JOINitem i (NOLOCK)
ONi.item = j.item
JOINwc (NOLOCK)
ONwc.wc = jt.wc
LEFT JOINjrt_sch js (NOLOCK)
ONjs.job = i.job
ANDjs.suffix = 1
ANDjs.oper_num = jt.oper_num
LEFT JOINjrt_sch js2 (NOLOCK)
ONjs2.job = i.job
ANDjs2.suffix = 0
ANDjs2.oper_num = jt.oper_num
LEFT JOINjrt_sch js3 (NOLOCK)
ONjs3.job = jt.job
ANDjs3.suffix = jt.suffix
ANDjs3.oper_num = jt.oper_num
LEFT JOIN(SELECT MIN(trans_num) AS '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
WHEREjt.trans_class = 'J'
ANDISNULL(jt.posted,0) = 1-- Only show posted transactions
GO
The more I look at this code the more irregularities I spot. Oh, the joys of inheriting code!
I'll see what I can do about generating some sample data for the table.