Member Avatar for SQLUser

I have two record sets. One is transactions and another is GL posting. I am aiming to create a third table to find out the customers are from which country inflate our GL.

For example GL Table has following fields
GLCode
GLDescription
GLAccount
GLAccountCCY
Balance
BalanceUSD

Transaction Table has the following fields
TxnID
TxnDate
ProductID
TxnAmount
TxnCCY
TxnAmountUSD
CustID
GLAccount

Some GLAccounts are straight populated from transaction and some are not. Hence when I take a sum of TxnAmountUSD on GLAccount and TxnCCY and try to match with the sum of BalanceUSD on GLAccount and GLAccountCCY from GL Table it does not match.

The third table I need to create is a reconciliation table and requirement is if I take total of GL table on GLCode it should match with the GLCode in newly crated table (ReconTable). ReconTable should contain all the records from Transaction table. In addition to that new records will be inserted for each GLAccount and CCY combination whose amount does not match with GL.

I am thinking of using CURSOR. The cursor will read value from transaction table and match the figure with GL. If does not match, will create a record with the difference value and insert the ReconTable along with the transaction records. Is this a right approach or is there a better way out?

  • 2 Contributors
  • 1 Reply
  • 131 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by davidcairns
Member Avatar for davidcairns

A simple insert should do the job just as well.

Assuming your table has the simplest possible definition

Reconciliation table
GLAccount
TxnID
ReconciliationType (Txn or Rec)
TxnDate
TxnAmount

You could write it as follows (example uses two statements, you could use one with a union clause if you like)

INSERT INTO Reconciliation (GLAccount, TxnID, ReconciliationType, TxnDate, TxnAmount)
SELECT GLAccount, TxnID, 'Txn', TxnDate, TxnAmount
FROM Transaction

INSERT INTO Reconciliation (GLAccount, TxnID, ReconciliationType, TxnDate, TxnAmount)
SELECT g.GLAccount, Null, 'Rec', getdate(),
Sum(g.BalanceUSD) - Sum(isnull(t.TxnAmountUSD, 0))
FROM GL as g
LEFT JOIN
Transaction as t
ON g.GLAccount = t.GLAccount
GROUP BY g.GLAccount
HAVING Sum(g.BalanceUSD) - Sum(isnull(t.TxnAmountUSD, 0)) <> 0

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.