I’m not sure if I understood correctly the logic/meaning of the desired output. Could you please explain it, with words ?
How exactly do you identify what is the OUT record for a given IN record ? what is the criteria ?
I’m guessing that a step_id=71 means IN, and a step_id=73 means OUT, and that for a given IN record, the corresponding OUT record is the one that has step_id=73 with the first transaction_no that is greater than its transaction_no.
I’m also guessing that a decision = 0 means that this IN record has no corresponding OUT record.
If I’m correct, a query like this could do the trick:
SELECT a.application_no,a.transaction_date DATE_IN, b.transaction_date DATE_OUT
FROM bpc_applications_transactions a, bpc_applications_transactions b
WHERE a.application_no = b.application_no
AND a.dept_code = 7
AND a.step_id = 71
AND b.step_id = 73
AND b.transaction_no = (SELECT MIN(transaction_no) FROM bpc_applications_transactions WHERE application_no = a.application_no AND step_id = 73 AND transacTion_no > a.transaction_no)
WHERE dept_code = 7
AND step_id = 71
AND decision = 0;
I currently don’t have a database to test it, so please make the necessary corrections.
If this is not what you need, please provide more details.