MSSQL: Alternative Approach to the below code, this is taking too much time to get results

Question

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

1. 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.

2. 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.

3. Need to avoid repetition.

4. 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.

5. 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;
   

Answer 1

Here's an approach where you

• create a derived table with the latest value for each combination of system_id and field_changed
• join that derived tabled to the original api table and get the values into individual fields

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