Help with ETL
-
I am dealing with a situation to where I need to bring payment (check), invoice, and credit association information from SystemA to SystemB. The problem is, each system handles this data differently.
In SystemA, you have credits (that kind of show as negative amount invoices) associated with a payment, and in SystemB a credit is associated with an invoice (the credit essentially pays off the invoice the way you would expect a payment), but all under the umbrella of a given payment (check number).
That was probably a poor attempt at explaining, and an example should make this easier to illustrate the problem:
--CREATE TABLE
CREATE TABLE DocApplyInformationFromSystemA (DocNum VARCHAR(20),DocType VARCHAR(10),CheckNumber VARCHAR(20),ApplyAmount NUMERIC(12,2),PaymentAmount NUMERIC(12,2))Sample data:
INSERT INTO DocApplyInformationFromSystemA
SELECT 'INV0001','Invoice','0000001',3.00,10.00
UNION
SELECT 'INV0002','Invoice','0000001',5.00,10.00
UNION
SELECT 'INV0003','Invoice','0000001',8.00,10.00
UNION
SELECT 'CRD0001','Credit','0000001',-2.75,10.00
UNION
SELECT 'CRD0002','Credit','0000001',-3.25,10.00This is how SystemA deals with a check that is paying off invoices and clearing out credits. Essentially, the system views it as "this payment with these credits clear out all of these invoices". You'll note that the payment amount is $10, but total of invoices $16. The "negative amount invoices" (basically credits) "inflates" the payment amount by $6 to $16 to cover the amount of invoices.
In SystemB, you can't associate a check with a negative amount invoice/credit like this and the credit needs associated with an invoice so here is an example of what this should look like in SystemB:
--CREATE TABLE
CREATE TABLE DocApplyInformationFromSystemB (ApplyFrom VARCHAR(20),ApplyTo VARCHAR(20),ApplyAmount NUMERIC(12,2))
INSERT INTO DocApplyInformationFromSystemB
SELECT '0000001','INV0001',3.00
UNION
SELECT '0000001','INV0002',5.00
UNION
SELECT '0000001','INV0003',2.00 --2.00 WAS REMAINING ON PAYMENT SO 6.00 LEFT ON INVOICE, NOW USE CREDITS TO PAY OFF REMAINING 6.00 ON THIS INVOICE
UNION
SELECT 'CRD0001','INV0003',2.75
UNION
SELECT 'CRD0002','INV0003',3.25Hopefully, I explained that well enough. There are other details and such, but I think they're largely irrelevant to this problem that I've watered down as much as possible.
The actual scenario is much more complicated with thousands of checks, and each check might be paying off hundreds of invoices with credits involved under the umbrella of a given check.
I went through a proof of concept scenario involving cursors and while loops to loop through a given check, then loop through credits for a check and loop through the invoices for the check and rewrite the apply lines for each loop iteration, and I got a very isolated scenario to work so I think I could continue down this path as a solution, but it is dreadfully slow and not a design approach I'd prefer to stick with anyway.
Anyone willing to give this a shot? Let me know if I need to clarify anything. Thanks in advance.
-
I can summarise the above as: "I have some data spread across multiple tables. I've written a cursor which can combine the data, but it's slow and does not work in all cases. Please help."
Well done, however, for providing the sample data in consumable format.
What we also need to see is what you want the results to look like, based on the sample data.
What is also unclear, is why the title of the post is 'Help with ETL', when it appears that all you are asking for is some T-SQL assistance? Please clarify.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Ultimately what I have in DocApplyInformationFromSystemB in original post is what it needs to look like so the DocApplyInformationFromSystemA is the before and the DocApplyInformationFromSystemB is the after that I need to create from DocApplyInformationFromSystemA. My apologies for not being clear.
- lbrigham - Thursday, October 25, 2018 7:30 AM
And now it's my turn to apologise for not being clear.
Please provide a copy of your desired results, based on the provided sample data. This can be as a SELECT query, or simply an image.
Also, I still don't understand what the ETL part of this is.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Forgive the ETL portion of the title. This is overall part of a larger ETL process, but this specific step is really just TSQL so title should've just "Help with TSQL"
This is the starting point:
--CREATE TABLE
CREATE TABLE DocApplyInformationFromSystemA (DocNum VARCHAR(20),DocType VARCHAR(10),CheckNumber VARCHAR(20),ApplyAmount NUMERIC(12,2),PaymentAmount NUMERIC(12,2))
--SAMPLE DATA
INSERT INTO DocApplyInformationFromSystemA
SELECT 'INV0001','Invoice','0000001',3.00,10.00
UNION
SELECT 'INV0002','Invoice','0000001',5.00,10.00
UNION
SELECT 'INV0003','Invoice','0000001',8.00,10.00
UNION
SELECT 'CRD0001','Credit','0000001',-2.75,10.00
UNION
SELECT 'CRD0002','Credit','0000001',-3.25,10.00End goal is this:
-
This just doesn't seem realistic. Can you imagine the grief that would ensue after a customer sees a whole new set of credits on their account that bear no resemblance to actual transactions on their account? Sounds like a really really bad idea to me. I know I wouldn't want a company I do business with to be so cavalier in their business practices that it is suddenly "okay" to just distort the heck out of my transaction history by "inventing fake credits" as an "accounting trick" of sorts. That's bad for business and bad all the way around. I'd be looking to only transfer account balances between systems, as anything else is, from my perspective, "book cookery"....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Yeah, ultimately it's due to how the different systems handle applying credits and payments to invoices. In SystemA, credits are essentially applied against payments kind of inflating the payment (which that concept is what makes no sense to me, but the idea is that customer says here's a payment and along with these 5 credits, this covers these 100 invoices or what have you), but in SystemB you're not able to do that and credits must be applied directly to invoices. Unfortunately, SystemA I have no control over as it's some Wells Fargo system. SystemB is what I have more control over. They'll have a report they can reference to see what in SystemA corresponds to what in SystemB. The whole design is not ideal, and I was pulled in midstream. They have invoices going back a few months that remain open now because of this ongoing issue.
- lbrigham - Thursday, October 25, 2018 8:54 AM
I know it doesn't help you in the slightest, but that's the fault of the solution architect for saying "okay" to that methodology. I'd still be in favor of applying a single "adjustment" credit to each account to make each balance correct. I would not want to have to try and apply to any level other than either account balance (ideal) or to the individual invoices. Is any of this even double-entry accounting? Heck, I'd probably look to create a single new invoice with the appropriate positive or negative amount, if I knew enough about how the whole shootin' match worked.
EDIT: spelling fix.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
One thing I would add is instead of having a paycheck amount, having a paycheck ID that links to a paychecks table. This normalises the design better. The would be the last column of your system A. Check number is not the item to focus on as the same check number can exist across issuing institutions.
----------------------------------------------------
- lbrigham - Thursday, October 25, 2018 8:26 AMForgive the ETL portion of the title. This is overall part of a larger ETL process, but this specific step is really just TSQL so title should've just "Help with TSQL"
This is the starting point:
--CREATE TABLE
CREATE TABLE DocApplyInformationFromSystemA (DocNum VARCHAR(20),DocType VARCHAR(10),CheckNumber VARCHAR(20),ApplyAmount NUMERIC(12,2),PaymentAmount NUMERIC(12,2))
--SAMPLE DATA
INSERT INTO DocApplyInformationFromSystemA
SELECT 'INV0001','Invoice','0000001',3.00,10.00
UNION
SELECT 'INV0002','Invoice','0000001',5.00,10.00
UNION
SELECT 'INV0003','Invoice','0000001',8.00,10.00
UNION
SELECT 'CRD0001','Credit','0000001',-2.75,10.00
UNION
SELECT 'CRD0002','Credit','0000001',-3.25,10.00End goal is this:
I am not sure I like this design as it is not additive. THe amount that is needed to clear the balances is $10. The first three lines achieve that, and the last two just seem out of place. Overall these here do not add up to ten no matter how you sum it. I would stick with system A.
----------------------------------------------------
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply