Weekly Dynamic: GL/MDA SQL

This week’s Weekly Dynamic comes from Jeremy Lowell of DataRealized.com.
This SQL code is designed to join Multi-Dimensional Analysis (MDA) tables back to the GL. It’s a little complicated but it seems to work fine.
select distinct dta10100.GROUPID,
dta10200.CODEID,dta10200.POSTDESC,
gl20000.* from GL20000
LEFT OUTER JOIN dbo.DTA10100 ON dbo.DTA10100.JRNENTRY = dbo.GL20000.JRNENTRY AND dbo.DTA10100.ACTINDX = dbo.GL20000.ACTINDX AND DTA10100.SEQNUMBR = GL20000.SEQNUMBR AND GL20000.ORCTRNUM = DTA10100.DOCNUMBR LEFT OUTER JOIN dbo.DTA10200 ON dbo.DTA10200.DTAREF = dbo.DTA10100.DTAREF and GL20000.SEQNUMBR = GL20000.OrigSeqNum
UNION
select dta10100.GROUPID,dta10200.CODEID,dta10200.POSTDESC,
gl20000.* from GL20000 LEFT OUTER JOIN dbo.DTA10100 ON dbo.DTA10100.JRNENTRY = dbo.GL20000.JRNENTRY AND dbo.DTA10100.ACTINDX = dbo.GL20000.ACTINDX AND DTA10100.SEQNUMBR = GL20000.OrigSeqNum AND GL20000.ORCTRNUM = DTA10100.DOCNUMBR LEFT OUTER JOIN dbo.DTA10200 ON dbo.DTA10200.DTAREF = dbo.DTA10100.DTAREF and GL20000.SEQNUMBR <> GL20000.OrigSeqNum

Jeremy has an update to this that I've promoted from the comments:

In looking at the data in the GL20000 tables and the DTA10100 table, the SDK calls for joining the following columns in order to obtain the MDA data for the GL transaction: JRNENTRY, ACTINDX, SEQNUMBR.




In my experience, I had a terribly Cartesian product when I made that join. While I haven't yet tracked down why that's the case, I ended up doing the following:



I took all of the data out of each of the GP companies ~ 50 and persist them into tables in my staging database. For MDA data, I have one table for the DTA10100 and DTA10200 data and another table for the GL20000 and 30000 data.



Once I have that data, I update a column that I created (dexrowid_GL) via 8 different steps... Basically creating a "validity" of each join. That logic is below.



-8 -- Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumber, DocNumber and amount(dollars).



-7 -- Must match on AccountNumber, JournalEntry, TRXDate, DocNumber and amount(dollars).



-6 -- Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumber and DocNumber



-5 -- Must match on AccountNumber, JournalEntry, TRXDate and DocNumber



-4 -- Must match on AccountNumber, JournalEntry, TRXDate, SequenceNumbrer, amount(dollars) and the SequenceNumber MUST NOT equal the OriginatingSequenceNumber.



-3 -- Must match on AccountNumber, JournalEntry, TRXDate, amount(dollars) and the SequenceNumber MUST NOT equal the OriginatingSequenceNumber



-2 -- Must match on AccountNumber, JournalEntry, TRXDate and amount(dollars).



42 -- There is not enough data to join the MDA transaction to the GL transaction.



Thus far, I have less than one percent of records that will not join with the logic mentioned above. I'm still working on ironing out why I have to jump through all of these hoops and I'm quite certain that it's a 3rd party application but I have yet to prove that out.



Good luck in accurately joining your MDA transactions to your GL transactions.... what should be fairly straight forward per the SDK has proven to be anything but for the dataset that I'm working against