CODEWITHSUNDEEP
sql-serverfinancebanking
TaylorN
TaylorN

Reputation: 399

Identify Inter-Account Transfers in SQL

I have a bunch of bank transactions in a table in SQL.

Example: http://sqlfiddle.com/#!6/6b2c8/1/0

I need to identify the transactions that are made between these 2 linked accounts. The Accounts table (not shown) links these 2 accounts to the one source (user).

For example: I have an everyday account, and a savings account. From time to time, I may transfer money from my everyday account, to my savings account (or vice-versa).

The transaction descriptions are usually similar (Transfer to xxx/transfer from xxx), usually on the same day, and obviously, the same dollar amount.

EDIT: I now have the following query (dumbed down), which works for some scenarios

Basically, I created 2 temp tables with all withdrawals and deposits that met certain criteria. I then join them together, based on a few requirements (same transaction amount, different account # etc). Then using the ROW_NUMBER function, I have ordered which ones are more likely to be inter-account transactions.

I now have an issue where if, for example:

$100 transferred from Account A to Account B

$100 Transferred from Account B to Account C

My query will match the transfer between Account A and C, then there is only one transaction for account B, and it will not be matched. So essentially, instead of receiving 2 rows back (2 deposits, lined up with 2 withdrawals), I only get 1 row (1 deposit, 1 withdrawal), for a transfer from A to B :(

INSERT  INTO #Deposits
        SELECT  t.*
        FROM    dbo.Customer c
                INNER JOIN dbo.Source src ON src.AppID = app.AppID
                INNER JOIN dbo.Account acc ON acc.SourceID = src.SourceID
                INNER JOIN dbo.Tran t ON t.AccountID = acc.AccountID
        WHERE   c.CustomerID = 123
                AND t.Template = 'DEPOSIT'

INSERT  INTO #Withdrawals
        SELECT  t.*
        FROM    dbo.Customer c
                INNER JOIN dbo.Source src ON src.AppID = app.AppID
                INNER JOIN dbo.Account acc ON acc.SourceID = src.SourceID
                INNER JOIN dbo.Tran t ON t.AccountID = acc.AccountID
        WHERE   c.CustomerID = 123
                AND t.Template = 'WITHDRAWAL'

;WITH    cte
          AS ( SELECT   [...] , 
                        ROW_NUMBER() OVER ( PARTITION BY d.TranID ORDER BY SUM( CASE WHEN d.TranDate = d.TranDate THEN 2 ELSE 1 END), w.TranID  ) AS DepRN,
                        ROW_NUMBER() OVER ( PARTITION BY w.TranID ORDER BY SUM( CASE WHEN d.TranDate = d.TranDate THEN 2 ELSE 1 END ), d.TranID ) AS WdlRN
               FROM     #Withdrawal w
                        INNER JOIN d ON w.TranAmount = d.TranAmount -- Same transaction amount
                                               AND w.AccountID <> d.AccountID -- Different accounts, same customer
                                               AND w.TranDate BETWEEN d.TranDate AND DATEADD(DAY, 3, d.TranDate) -- Same day, or within 3 days
               GROUP BY [...]
             )
    SELECT  *
    FROM    cte
    WHERE cte.DepRN = cte.WdlRN

Upvotes: 0

Views: 1335

Answers (1)

shawnt00
shawnt00

Reputation: 17925

Maybe this is a start? I don't think we have enough info to say whether this would be reliable or would cause a lot of "false positives".

select t1.TransactionID, t2.TransactionID
from dbo.Transactions as t1 inner join dbo.Transactions as t2
    on      t2.AccountID = t2.AccountID
        and t2.TransactionDate = t1.TransactionDate
        and t2.TransactionAmount = t1.TransactionAmount
        and t2.TransactionID - t1.TransactionID between 1 and 20 -- maybe??
        and t1.TransactionDesc like 'Transfer from%'
        and t2.TransactionDesc like 'Transfer to%'
        and t2.TransactionID > t1.TransactionID

Upvotes: 1

Related Questions