all. Ive been working with some Sales Force data to solve some data synchronization problems we have between two instances of SFDC.
We have two instances of SFDC (SalesForce) that need to mirror each other. They are not linked however. A row is created in SFDCA by one group, second group creates associated (copy) row in SFDCB linking with a unique ID created in SFDCA. Group A and Group B then go on to edit rows and the data quickly diverges.
I am trying to correct the divergence by daily looking at each row and comparing the last modified dates of three key columns. Which ever column was the most recently modified will then have its value pasted into the other system.
So, I have to tables. From the example data below, I'd like to identify where the attribute values for a given deal id/attribute pair don't match and also the date/time of the most recent change to that attribute.
So, on deal ID 123, I would expect my query to show the Max Modified Date (in italics below) for the column Book Date from SFDC-A and SFDC-B displayed next to each other along with the associated value like so:
SFDCA[123, Book Date, 1/1/19, 7/29/19 1:00:00 PM], SFDCB[123, Book Date, 1/2/19, 7/29/19 2:00:00 PM]
I can then use this information to make the decision to overwrite SFDC-A with the value from SFDC-B for book date since it was more recently modified.
I've pasted my mostly working query below. I'd love some input on whether I could do this more elegantly. Running this in Access with 16MB of RAM doesnt scale well to 10K+ rows. Maybe its a good query but it just needs an actual SQL Server to run it...either way, I'd appreciate your input.
Deal ID (Liking Attribute), Field Name, Field Value, Modified Date 123, Book Date, 1/1/19, *7/29/19 1:00:00 PM* 199, Stage, Commit - 90%, 7/16/19 03:00:00 AM 123, Book Date, 12/31/18, 12/15/18 12:00:00 PM
Deal ID (Liking Attribute), Field Name, Field Value, Modified Date 123, Book Date, 1/2/19, *7/29/19 2:00:00 PM* 199, Stage, Propose - 10%, 7/14/19 05:00:00 PM 123, Book Date, 12/10/18, 11/1/18 2:30:00 PM
SELECT SFDCA.[Deak ID], SFDCB.[Deal ID], SFDCA.[Field / Event], SFDCB.[Field / Event], SFDCA.[Edit Date], SFDCB.[Edit Date] FROM SFDCA LEFT JOIN SFDCB ON SFDCA.[Deal ID] = SFDCB.[Deal ID] WHERE SFDCA.[Field / Event] = SFDCB.[Field / Event] AND SFDCB.[Edit Date] = (SELECT MAX(SFDCB1.[Edit Date]) FROM SFDCB AS SFDCB1 WHERE SFDCB1.[Deal ID] = SFDCB.[Deal ID] AND SFDCB1.[Field / Event] = SFDCB.[Field / Event]) AND SFDCA.[Edit Date] = (SELECT MAX(SFDCA1.[Edit Date]) FROM SFDCA AS SFDCA1 WHERE SFDCA1.[Deal ID] = SFDCA.[Deal ID] AND SFDCA1.[Field / Event] = SFDCA.[Field / Event]);