Hi all. I've got a query that pulls data from a Progressor database. I need to get the customer and supplier updates from before a certain date. This is a query that will be run from another application so it will be run regularly.
Progressor has an audit table. It is a field level audit table. So, let's assume I have a supplied record and I change address lines 1, 2, and 3, the audit table will end up with 3 rows in it. I then need to be able to select the records and some data from some other joined tables and return to my app. Simple enough as the audit table joins on the supplier table supplier ID.
Select PP_SUPP.PP_SUP_NO SupplierCode,
join PP_SAD on PP_SUPP.PP_SUP_NO = PP_SAD.PP_SUP_NO and PP_SAD_TYPE = 'INV'
join GD_CST on PP_SAD.PP_SAD_REF = GD_CST.GD_CST_CODE
join PS_CTRY on PP_SUPP.PP_SUP_CTRY = PS_CTRY.PS_CTR_CODE
join PS_CURR on PP_SUPP.PP_SUP_CURR = PS_CURR.PS_CUR_CODE
join PP_CAT on PP_SUPP.PP_SUP_CAT = PP_CAT.PP_CAT_CODE
join GD_VATL on PP_SUPP.PP_SUP_VATCD = GD_VATL.GD_VAT_CODE
join PP_PAYM on PP_SUPP.PP_SUP_PAY = PP_PAYM.PP_PAY_CODE
where PP_SUPP.PP_SUP_NO in (select distinct AUD_CONT_CODE
where AUD_CONT_TYPE_DESC = 'Supplier'
and AUD_TEXT1 IN ('Country Changed', 'Supplier Address Created', 'Supplier Address Removed', 'Supplier Created', 'Supplier Currency Changed', 'VAT Code Changed', 'VAT Regn. Changed', 'Payment Method Changed')
and AUD_DATE_DT_D > '2019-01-01 00:00:00.000')
This query does just that. However, let's assume I add a new supplier to the database. It will add rows to that audit table, one row for each field. The audit table will contain a line with the supplier ID and the AUD_TEXT1 field will contain "Supplier Created", but it will also contain other lines. I need to know in my select statement in the main query, whether the record showing is a new supplier or an updated supplier.