I hope I can explain this so it makes sense.
I'm using a SQL DB that has two tables that track the application of A/R activity (Payments, Credits, Charges, refunds, invoices, etc.). Table 1 records the initial entry of the a/r item and table 2 records the amount of the application and where it was applied to and from. The problem I have is that the system allows a user to change where a payment/credit/ect. was applied but does not track it.
For example if check #2 for $100 was originally applied to cust 1 invoice 10, table 1 would record the the initial entry of check #2 for $100 and assign it a unique id. Table 2 would record the application of the $100 from check #2 (Using the unique id from table 1) to invoice 10 (using the unique id from table 1 created for this invoice) on customer 1. If a user then changed the application to be $25 to invoice 20 and $75 to invoice 30 on customer 1, table 2 would be updated to reflect the new application. There is not a new record created for the new application. The system reuses the existing uinque id's for Check #100 from table 1 and deletes the old application data to invoice 10 to reopen the invoice.
What I need to do is create a historical record of these changes so I can track if someone unapplies money and moves it somewhere else. I'm not sure the best way to do this but here is what I was thinking:
1. Create a backup of table 2.
2. Every night compare the backup to the current table and write any differences to a 3rd table.
3. Once step 2 is complete, overwrite the previous days backup with a backup of the current table.
4. Repeat this process, each day adding (not overwriting) the changed transactions to the 3rd table.
Is this the best approach and how would I create a script to say compare table2backup to table2 and add the records that no longer exist in the current table from the backup to the third table?
Thanks.
Start Free Trial
