I want to know how simply can write the code below and also i am interested to avoid code repetition. Is there any other way. Do's are
Whatever changes in the fields of [invoices_global] table, that changes would be inserted as new records in the format of old_value, new_value, field_changed in the Change log table.
A single field may be updated by many users, so latest should take, thats why i mentioned as last_updated_datetime desc in the code.
Need to avoid repetition.
Performance improvement, since i am calling the same table again and again multiple times for single ap_work_id. It takes 18sec to fetch results - 2000 out of 40000.
FYI, system_id and ap_work_id are same.
SELECT
api.ap_work_id,
api.invoice_status,
api.po_number,
api.po_value,
api.region,
api.invoice_value,
api.invoice_value_currency,
em3.employee_full_name AS invoice_owner,
(
**SELECT TOP 1
old_value
FROM change_log cl
WHERE field_changed = 'ARIBA INVOICE ID' AND api.ap_work_id = cl.
system_id AND system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS bot_ariba_id_value,
(
**SELECT TOP 1
new_value
FROM change_log cl
WHERE field_changed = 'ARIBA INVOICE ID' AND api.ap_work_id = cl.
system_id AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS processor_ariba_id_value,
(
**SELECT TOP 1
old_value
FROM change_log cl
WHERE field_changed = 'PO NUMBER' AND api.ap_work_id = cl.system_id AND
cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS bot_po_number,
(
**SELECT TOP 1
new_value
FROM change_log cl
WHERE field_changed = 'PO NUMBER' AND api.ap_work_id = cl.system_id AND
cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS processor_po_number,
(
**SELECT TOP 1
old_value
FROM change_log cl
WHERE field_changed = 'VENDOR NAME' AND api.ap_work_id = cl.system_id
AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS bot_vendor_name,
(
**SELECT TOP 1
new_value
FROM change_log cl
WHERE field_changed = 'VENDOR NAME' AND api.ap_work_id = cl.system_id
AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS processor_vendor_name,
(
**SELECT TOP 1
old_value
FROM change_log cl
WHERE field_changed = 'INVOICE VALUE' AND api.ap_work_id = cl.system_id
AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS bot_invoice_value,
(
**SELECT TOP 1
new_value
FROM change_log cl
WHERE field_changed = 'INVOICE VALUE' AND api.ap_work_id = cl.system_id
AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS processor_invoice_value,
(
**SELECT TOP 1
old_value
FROM change_log cl
WHERE field_changed = 'INVOICE CURRENCY' AND api.ap_work_id = cl.
system_id AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS bot_invoice_currency,
(
**SELECT TOP 1
new_value
FROM change_log cl
WHERE field_changed = 'INVOICE CURRENCY' AND api.ap_work_id = cl.
system_id AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS processor_invoice_currency,
(
**SELECT TOP 1
old_value
FROM change_log cl
WHERE field_changed = 'INVOICE DATE' AND api.ap_work_id = cl.system_id
AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS bot_invoice_date,
(
**SELECT TOP 1
new_value
FROM change_log cl
WHERE field_changed = 'INVOICE DATE' AND api.ap_work_id = cl.system_id
AND cl.system_type = 'INVOICE'
ORDER BY
last_updated_datetime DESC**
) AS processor_invoice_date
FROM invoices_global api
OUTER APPLY
(
SELECT TOP 1
*
FROM transactions t
WHERE t.po_number = api.po_number AND t.po_number <> ''
ORDER BY
t.last_updated_datetime DESC
) AS t
LEFT JOIN employee_master em3
ON em3.ldap = api.invoice_owner_ldap
LEFT JOIN vendor v
ON v.vendor_number = api.vendor_number
LEFT JOIN cost_center cc
ON cc.cost_ctr = api.cost_center
WHERE DATEDIFF(DAY,api.approved_date,GETDATE()) <= 30
GROUP BY
api.ap_work_id,
api.category,
api.invoice_status,
api.po_number,
api.po_value,
api.region,
em3.employee_full_name,
api.invoice_value,
api.invoice_value_currency,
api.invoice_date,
api.due_date,
api.company_code,
api.vendor_number,
api.approved_date,
t.company_code,
api.cost_center,
v.vendor_name,
v.vendor_number,
cc.company;
Here's an approach where you
It currently uses MAX(CASE()) but you could do this as a PIVOT.
Only the top part of the SQL (relevant to the problem) is included here - you'll need to do the other joins on the bottom. I also haven't tested it (without table structures etc) so I can't promise there are no typos.
; WITH LatestCL AS
(SELECT *
FROM (SELECT system_id AS ap_work_id, field_changed, old_value, new_value,
ROW_NUMBER () OVER (PARTITION BY system_id, field_changed ORDER BY last_updated_datetime DESC) AS rn
FROM change_log
WHERE system_type = 'INVOICE'
) cl
WHERE cl.rn = 1
)
SELECT api.ap_work_id,
api.invoice_status,
api.po_number,
api.po_value,
api.region,
api.invoice_value,
api.invoice_value_currency,
MAX(CASE WHEN field_changed = 'ARIBA INVOICE ID' THEN old_value ELSE NULL END) AS bot_ariba_id_value,
MAX(CASE WHEN field_changed = 'ARIBA INVOICE ID' THEN new_value ELSE NULL END) AS processor_ariba_id_value,
MAX(CASE WHEN field_changed = 'PO NUMBER' THEN old_value ELSE NULL END) AS bot_po_number,
MAX(CASE WHEN field_changed = 'PO NUMBER' THEN new_value ELSE NULL END) AS processor_po_number,
MAX(CASE WHEN field_changed = 'VENDOR NAME' THEN old_value ELSE NULL END) AS bot_vendor_name,
MAX(CASE WHEN field_changed = 'VENDOR NAME' THEN new_value ELSE NULL END) AS processor_vendor_name,
MAX(CASE WHEN field_changed = 'INVOICE VALUE' THEN old_value ELSE NULL END) AS bot_invoice_value,
MAX(CASE WHEN field_changed = 'INVOICE VALUE' THEN new_value ELSE NULL END) AS processor_invoice_value,
MAX(CASE WHEN field_changed = 'INVOICE CURRENCY' THEN old_value ELSE NULL END) AS bot_invoice_currency,
MAX(CASE WHEN field_changed = 'INVOICE CURRENCY' THEN new_value ELSE NULL END) AS processor_invoice_currency,
MAX(CASE WHEN field_changed = 'INVOICE DATE' THEN old_value ELSE NULL END) AS bot_invoice_date,
MAX(CASE WHEN field_changed = 'INVOICE DATE' THEN new_value ELSE NULL END) AS processor_invoice_date
FROM invoices_global api
INNER JOIN LatestCL ON api.ap_work_id = LatestCL.ap_work_id