---- 比較兩個表之間的差異並列出不同欄位
--相同key值為 comp_id + auto_no
SELECT
s1_log.comp_id,
s1_log.auto_no,
-- 每個欄位比對
CASE WHEN ISNULL(s1_log.apply_date, '') <> ISNULL(test.apply_date, '') THEN CONCAT('apply_date: ', s1_log.apply_date, ' <> ', test.apply_date) ELSE 'Same' END AS apply_date_diff,
CASE WHEN ISNULL(s1_log.cust_no, '') <> ISNULL(test.cust_no, '') THEN CONCAT('cust_no: ', s1_log.cust_no, ' <> ', test.cust_no) ELSE 'Same' END AS cust_no_diff,
CASE WHEN ISNULL(s1_log.apply_kind, '') <> ISNULL(test.apply_kind, '') THEN CONCAT('apply_kind: ', s1_log.apply_kind, ' <> ', test.apply_kind) ELSE 'Same' END AS apply_kind_diff,
CASE WHEN ISNULL(s1_log.apply_remark, '') <> ISNULL(test.apply_remark, '') THEN CONCAT('apply_remark: ', s1_log.apply_remark, ' <> ', test.apply_remark) ELSE 'Same' END AS apply_remark_diff,
CASE WHEN ISNULL(s1_log.emp_id, '') <> ISNULL(test.emp_id, '') THEN CONCAT('emp_id: ', s1_log.emp_id, ' <> ', test.emp_id) ELSE 'Same' END AS emp_id_diff,
CASE WHEN ISNULL(s1_log.comp_no, '') <> ISNULL(test.comp_no, '') THEN CONCAT('comp_no: ', s1_log.comp_no, ' <> ', test.comp_no) ELSE 'Same' END AS comp_no_diff,
CASE WHEN ISNULL(s1_log.dept_no, '') <> ISNULL(test.dept_no, '') THEN CONCAT('dept_no: ', s1_log.dept_no, ' <> ', test.dept_no) ELSE 'Same' END AS dept_no_diff,
CASE WHEN ISNULL(s1_log.bank_no, '') <> ISNULL(test.bank_no, '') THEN CONCAT('bank_no: ', s1_log.bank_no, ' <> ', test.bank_no) ELSE 'Same' END AS bank_no_diff,
CASE WHEN ISNULL(s1_log.bank_acc, '') <> ISNULL(test.bank_acc, '') THEN CONCAT('bank_acc: ', s1_log.bank_acc, ' <> ', test.bank_acc) ELSE 'Same' END AS bank_acc_diff,
CASE WHEN ISNULL(s1_log.note_no, '') <> ISNULL(test.note_no, '') THEN CONCAT('note_no: ', s1_log.note_no, ' <> ', test.note_no) ELSE 'Same' END AS note_no_diff,
CASE WHEN ISNULL(s1_log.lift_date, '') <> ISNULL(test.lift_date, '') THEN CONCAT('lift_date: ', s1_log.lift_date, ' <> ', test.lift_date) ELSE 'Same' END AS lift_date_diff,
CASE WHEN ISNULL(s1_log.inward_amt, 0) <> ISNULL(test.inward_amt, 0) THEN CONCAT('inward_amt: ', s1_log.inward_amt, ' <> ', test.inward_amt) ELSE 'Same' END AS inward_amt_diff,
CASE WHEN ISNULL(s1_log.rem_amt, 0) <> ISNULL(test.rem_amt, 0) THEN CONCAT('rem_amt: ', s1_log.rem_amt, ' <> ', test.rem_amt) ELSE 'Same' END AS rem_amt_diff,
CASE WHEN ISNULL(s1_log.inward_tot, 0) <> ISNULL(test.inward_tot, 0) THEN CONCAT('inward_tot: ', s1_log.inward_tot, ' <> ', test.inward_tot) ELSE 'Same' END AS inward_tot_diff,
CASE WHEN ISNULL(s1_log.sub_amt, 0) <> ISNULL(test.sub_amt, 0) THEN CONCAT('sub_amt: ', s1_log.sub_amt, ' <> ', test.sub_amt) ELSE 'Same' END AS sub_amt_diff,
CASE WHEN ISNULL(s1_log.remark, '') <> ISNULL(test.remark, '') THEN CONCAT('remark: ', s1_log.remark, ' <> ', test.remark) ELSE 'Same' END AS remark_diff,
CASE WHEN ISNULL(s1_log.keyin_emp, '') <> ISNULL(test.keyin_emp, '') THEN CONCAT('keyin_emp: ', s1_log.keyin_emp, ' <> ', test.keyin_emp) ELSE 'Same' END AS keyin_emp_diff,
CASE WHEN ISNULL(s1_log.keyin_date, '') <> ISNULL(test.keyin_date, '') THEN CONCAT('keyin_date: ', s1_log.keyin_date, ' <> ', test.keyin_date) ELSE 'Same' END AS keyin_date_diff,
CASE WHEN ISNULL(s1_log.pay_date, '') <> ISNULL(test.pay_date, '') THEN CONCAT('pay_date: ', s1_log.pay_date, ' <> ', test.pay_date) ELSE 'Same' END AS pay_date_diff,
CASE WHEN ISNULL(s1_log.pick_yn, '') <> ISNULL(test.pick_yn, '') THEN CONCAT('pick_yn: ', s1_log.pick_yn, ' <> ', test.pick_yn) ELSE 'Same' END AS pick_yn_diff,
CASE WHEN ISNULL(s1_log.snap_no, '') <> ISNULL(test.snap_no, '') THEN CONCAT('snap_no: ', s1_log.snap_no, ' <> ', test.snap_no) ELSE 'Same' END AS snap_no_diff,
CASE WHEN ISNULL(s1_log.pick_date, '') <> ISNULL(test.pick_date, '') THEN CONCAT('pick_date: ', s1_log.pick_date, ' <> ', test.pick_date) ELSE 'Same' END AS pick_date_diff
FROM ERP_02.dbo.aaa270_log s1_log
INNER JOIN ERP.dbo.aaa270 test
ON s1_log.comp_id = test.comp_id
AND s1_log.auto_no = test.auto_no