January 14, 2020 at 3:40 pm
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,
PP_SAD.PP_SAD_REF SupplierAddressCode,
GD_CST.GD_CST_FNAME SupplierFullName,
GD_CST.GD_CST_ADD1 SupplierAddressLine1,
GD_CST.GD_CST_ADD2 SupplierAddressLine2,
GD_CST.GD_CST_ADD3 SupplierAddressLine3,
GD_CST.GD_CST_ADD4 SupplierAddressLine4,
GD_CST.GD_CST_ADD5 SupplierAddressLine5,
GD_CST.GD_CST_ADD6 SupplierAddressLine6,
GD_CST.GD_CST_ADD7 SupplierAddressLine7,
GD_CST.GD_CST_ADD8 SupplierAddressLine8,
GD_CST.GD_CST_ADD9 SupplierAddressLine9,
GD_CST.GD_CST_ADD10 SupplierAddressLine10,
PP_SUPP.PP_SUP_CURR SupplierCurrency,
PS_CURR.PS_CUR_DESC SupplierCurrencyDesc,
PP_SUPP.PP_SUP_CTRY SupplierCountry,
PS_CTRY.PS_CTR_DESC SupplierCountryDesc,
PP_SUPP.PP_SUP_CAT SupplierCategory,
PP_CAT.PP_CAT_DESC SupplierCategoryDesc,
PP_SUPP.PP_SUP_DEACTIVEATE_DATE SupplierDeactivatedDate,
PP_SUPP.PP_SUP_VAT SupplierVATNo,
PP_SUPP.PP_SUP_VATCD SupplierVATCode,
GD_VATL.GD_VAT_DESC SupplierVATCodeDesc,
PP_SUPP.PP_SUP_SDUE SupplierPaymentTerms,
PP_SUPP.PP_SUP_ODUE OurPaymentTerms,
PP_SUPP.PP_SUP_PAY PaymentMethod,
PP_PAYM.PP_PAY_DESC PaymentMethodDesc
from PP_SUPP
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
from CONT_LOG
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.
Any thoughts?
Regards,
Lee
January 15, 2020 at 2:59 pm
You have a date field that you are querying on. Can't you check to see if the CreatedDate is > than that date. That would mean it was created after the last run
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 15, 2020 at 3:43 pm
I don't have a created date, that's the problem. In the audit table a record gets entered to say "customer created" and there is a date stamp on that, but at the same time a customer is created, several other rows get put into that same table to say other things have been created, so joining the customer table to the audit table yields a one to many relationship. That means I need to query the audit table to get a list of IDs that are distinct to then do the other query. The trouble is, when I do the other query, I can't see if that record requires an insert or update in the other system.
For example, I create a customer with a code A1. At the same time I create an address and a phone number record on it and save. The audit table will have a customer created entry, another for the phone record, another for the address record, and several other audited fields on the customer record will have individual entries in the audit table, so when I query the audit table for any records after a given datetime, I would get potentially 5 or more records for A1. If I then do a separate query using the distinct values from the first query, I just get the data I need for the other system, but no idea if it is an update or an insert from that query.
If the audit table ONLY contained "customer created" or "customer updated", this would be easy, but because it audits several fields on the customer table I can't do the join.
Regards,
Lee
January 15, 2020 at 4:50 pm
Without sample data/expected results, it's pretty difficult to come up with a solution. I suspect that you'll either want to use a CROSS APPLY/OUTER APPLY with a TOP(1) or a CTE with a ROW_NUMBER() to pull the information from the latest relevant record.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 15, 2020 at 4:52 pm
Yeah, I've spent today trying to get some data for my dev system. The trouble is the database this sits in, is around 200gb in size which is far bigger than what I can deal with on my dev system. Extracting just data from the tables in question gives me a 3.2gb SQL script of inserts that populate just these few tables and it just kills everything when trying to process it.
Regards,
Lee
January 15, 2020 at 6:49 pm
Select PP_SUPP.PP_SUP_NO SupplierCode,
PP_SAD.PP_SAD_REF SupplierAddressCode,
GD_CST.GD_CST_FNAME SupplierFullName,
GD_CST.GD_CST_ADD1 SupplierAddressLine1,
GD_CST.GD_CST_ADD2 SupplierAddressLine2,
GD_CST.GD_CST_ADD3 SupplierAddressLine3,
GD_CST.GD_CST_ADD4 SupplierAddressLine4,
GD_CST.GD_CST_ADD5 SupplierAddressLine5,
GD_CST.GD_CST_ADD6 SupplierAddressLine6,
GD_CST.GD_CST_ADD7 SupplierAddressLine7,
GD_CST.GD_CST_ADD8 SupplierAddressLine8,
GD_CST.GD_CST_ADD9 SupplierAddressLine9,
GD_CST.GD_CST_ADD10 SupplierAddressLine10,
PP_SUPP.PP_SUP_CURR SupplierCurrency,
PS_CURR.PS_CUR_DESC SupplierCurrencyDesc,
PP_SUPP.PP_SUP_CTRY SupplierCountry,
PS_CTRY.PS_CTR_DESC SupplierCountryDesc,
PP_SUPP.PP_SUP_CAT SupplierCategory,
PP_CAT.PP_CAT_DESC SupplierCategoryDesc,
PP_SUPP.PP_SUP_DEACTIVEATE_DATE SupplierDeactivatedDate,
PP_SUPP.PP_SUP_VAT SupplierVATNo,
PP_SUPP.PP_SUP_VATCD SupplierVATCode,
GD_VATL.GD_VAT_DESC SupplierVATCodeDesc,
PP_SUPP.PP_SUP_SDUE SupplierPaymentTerms,
PP_SUPP.PP_SUP_ODUE OurPaymentTerms,
PP_SUPP.PP_SUP_PAY PaymentMethod,
PP_PAYM.PP_PAY_DESC PaymentMethodDesc,
ISNULL(x.SupplierCreated,0) as SupplierCreated
from PP_SUPP
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
outer apply(SELECT TOP(1)
1 SupplierCreated
FROM CONT_LOG
where AUD_CONT_TYPE_DESC = 'Supplier'
and AUD_TEXT1 = 'Supplier Created'
and AUD_DATE_DT_D > '2019-01-01 00:00:00.000') x
where PP_SUPP.PP_SUP_NO in (select AUD_CONT_CODE
from CONT_LOG
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')
January 15, 2020 at 9:05 pm
The whole purpose of having a CROSS APPLY/OUTER APPLY with a TOP(1) is so that you can avoid multiple reads of the table (although the sort may offset that advantage to some degree). I've modified Jonathan's query to show how that would be done.
Select PP_SUPP.PP_SUP_NO SupplierCode,
PP_SAD.PP_SAD_REF SupplierAddressCode,
GD_CST.GD_CST_FNAME SupplierFullName,
GD_CST.GD_CST_ADD1 SupplierAddressLine1,
GD_CST.GD_CST_ADD2 SupplierAddressLine2,
GD_CST.GD_CST_ADD3 SupplierAddressLine3,
GD_CST.GD_CST_ADD4 SupplierAddressLine4,
GD_CST.GD_CST_ADD5 SupplierAddressLine5,
GD_CST.GD_CST_ADD6 SupplierAddressLine6,
GD_CST.GD_CST_ADD7 SupplierAddressLine7,
GD_CST.GD_CST_ADD8 SupplierAddressLine8,
GD_CST.GD_CST_ADD9 SupplierAddressLine9,
GD_CST.GD_CST_ADD10 SupplierAddressLine10,
PP_SUPP.PP_SUP_CURR SupplierCurrency,
PS_CURR.PS_CUR_DESC SupplierCurrencyDesc,
PP_SUPP.PP_SUP_CTRY SupplierCountry,
PS_CTRY.PS_CTR_DESC SupplierCountryDesc,
PP_SUPP.PP_SUP_CAT SupplierCategory,
PP_CAT.PP_CAT_DESC SupplierCategoryDesc,
PP_SUPP.PP_SUP_DEACTIVEATE_DATE SupplierDeactivatedDate,
PP_SUPP.PP_SUP_VAT SupplierVATNo,
PP_SUPP.PP_SUP_VATCD SupplierVATCode,
GD_VATL.GD_VAT_DESC SupplierVATCodeDesc,
PP_SUPP.PP_SUP_SDUE SupplierPaymentTerms,
PP_SUPP.PP_SUP_ODUE OurPaymentTerms,
PP_SUPP.PP_SUP_PAY PaymentMethod,
PP_PAYM.PP_PAY_DESC PaymentMethodDesc,
x.SupplierCreated
from PP_SUPP
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
CROSS apply(SELECT TOP(1)
CASE WHEN AUD_TEXT1 = 'Supplier Created' THEN 1 ELSE 0 END SupplierCreated
FROM CONT_LOG
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'
ORDER BY SupplierCreated DESC) x
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 16, 2020 at 8:57 am
Thanks guys. It's one of those things where you "feel" like there is a way to do it. I will try these and give it a whirl and let you know.
/L
Regards,
Lee
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply