CODEWITHSUNDEEP
sqloracle-databaseaggregateaggregate-functions
Amir
Amir

Reputation: 113

Balance Negative for 3 months

I have 3 tables. Account. Trans and Balance.

Let's say account has 2 accounts only. Account Number 1 and 2.

Both of them have multiple transactions in Trans Table.

Sum(Amount) in Trans table equals balance at any given time for each account.

For Example: Account 1 and 2 both had $50. Account 1 did transactions for $100 and Account 2 did transactions for $35 only.

Now, Account 1 has a balance of Negative $50. Let's say he deposited $20. Regardless, balance is still negative.

I need a query which checks that balance is negative for consecutive 3 months or more from today's date. (so sysdate whenever I would run this). Although he had a deposit of $20, the balance is still negative.

When I use transdate from trans table or lastupdate from balance table as a 3 month criteria, the above account DOES NOT get picked up. Although, it is negative but it sees a transaction.

I was wondering how can I query where it would display account number and balance only if it is negative for consecutive 3 months or more regardless of when the transaction occur.

Columns:

 Account Table has AccountID
 Trans Table has AccountID, Amount, TransDate
 Balance Table has AccountID, Balance, LastUpdate

Thanks

Update

Trans Table
Select * from trans where accountid = 1;

Transdate  Merchant    Amount   AccountID
10/1/16    Employer    50       1
10/4/16    Walmart     -20      1
10/7/16    Kroger      -50      1

Now, his account went negative -$20 on 10/7/2016.

 Transdate  Merchant    Amount   AccountID
 12/01/16   Employer    10       1

His Account is still negative. If I run the query today (01/15/2017) or later, his account should get picked up because he still has negative balance for at least 90 days. 

Balance Table

It keeps only 1 record per Account. As of today, it shows the following:

AccountID    Balance    LastUpdate
1            -10        12/01/2016

LastUpdate is the same date as the last date of transaction in Trans Table for that Account.

I am going crazy on how to find the accounts who have been negative for 90 days or more even if the last deposit came yesterday but the accounts are still negative. I have 50,000 accounts that I need to do this for....

Upvotes: 2

Views: 205

Answers (1)

Serge
Serge

Reputation: 4036

We are going to calculate a Running Balance for all transactions. We will then select the most recent transaction for each account, and filter these down to only those where:

  1. most recent transaction is more than 90 days old; or
  2. the maximum balance in the past 90 days is less than 0.
;
WITH running_total AS (

        SELECT   t.AccountID
                ,t.Transdate
                ,t.Amount
                ,SUM(t.Amount) OVER (PARTITION BY t.AccountID ORDER BY t.Transdate ROWS UNBOUNDED PRECEDING) AS RunningTotal
        FROM (
                -- Aggregate transctions by Account and Date
                SELECT   t.AccountID
                        ,t.Transdate
                        ,SUM(t.Amount) AS Amount
                FROM Trans
                GROUP BY t.AccountID
                        ,t.Transdate
                ) t

)

SELECT *
FROM running_total rt
WHERE 
-- Most recent transaction
rt.Transdate = (
        SELECT MAX(Transdate)
        FROM running_total
        WHERE AccountID = rt.AccountID
)
-- Current balance is negative
AND rt.RunningTotal < 0

-- Get maximum balance for all transctions in the past 90 days
-- If none found (most recent transaction more than 90 days old),
-- substitute -1
AND COALESCE((
        SELECT MAX(RunningTotal)
        FROM running_total
        WHERE AccountID = rt.AccountID
        AND Transdate >= DATEADD(DAY, -90, GETDATE())
), -1) < 0

Upvotes: 0

Related Questions