CODEWITHSUNDEEP
sqloracle-database
user3352171
user3352171

Reputation: 53

Return account balances only if a date is greater than 30 days in the past

I have table accounts that has customer ID's in column account_ID with multiple account balances per ID in column account_balance. There are also multiple statement dates per ID, column account_statement_date.

I need to return the ID and account balance if the account balance is greater than $50.00 and if the most recent statement date is greater than 30 days in the past.

This will return the ID and account balance if the account balance is greater than $50.00:

select account_ID, sum(account_balance)
from accounts
where account_balance > 50
group by account_ID

How do I refine the query to only return the ID and account balance if the account balance is greater than $50.00 and if the most recent statement date is greater than 30 days in the past?

Upvotes: 0

Views: 1262

Answers (4)

ErikE
ErikE

Reputation: 50251

If your data looks like this:

account_ID  account_statement_date  account_balance
1529        2014-12-01              $40.00
1529        2015-01-01              $60.00
1529        2015-02-01              $65.00 -- < 30 days
2647        2014-12-01              $20.00
2647        2015-01-01              $25.00 -- > 30 days but < $50
3198        2014-12-01              $10.00
3198        2015-01-01              $50.00 -- > 30 days and >= $50

And the correct answer when run on 2015-02-01 is:

account_ID  account_statement_date  account_balance
3198        2015-01-01              $50.00

then I think the query you want might look something like this:

SELECT
   a.account_ID,
   a.account_statement_date,
   a.account_balance
FROM
   (
      SELECT
         row_number() OVER (PARTITION BY account_ID ORDER BY account_statement_date DESC) AS latest,
         account_ID,
         account_statement_date,
         account_balance
      FROM
         accounts
   ) AS a
WHERE
   a.account_statement_date < sysdate - 30
   AND a.latest = 1
   AND a.account_balance >= 50
;

But, if your data looks like this:

account_ID  account_balance_date account_statement_date  account_balance
1529        2014-12-10           2015-01-01              $40.00
1529        2015-12-16           2015-01-01              $60.00
1529        2015-01-10           2015-02-01              $30.00
1529        2015-01-19           2015-02-01              $65.00 -- < 30 days
2647        2014-12-25           2015-01-01              $20.00
2647        2014-12-30           2015-01-01              $25.00 -- > 30 days
2647        2014-01-02           NULL                    $75.00
2647        2014-01-15           NULL                    $20.00 -- but < $50
3198        2014-12-14           2015-01-01              $20.00
3198        2014-12-30           2015-01-01              $25.00 -- > 30 days
3198        2014-01-09           NULL                    $20.00
3198        2014-01-22           NULL                    $50.00 -- and >= $50!

And the correct answer when run on 2015-02-01 is:

account_ID  last_account_statement_date  last_account_balance
3198        2015-01-01                   $50.00

then I think the query you want might look something like this:

SELECT
   a.account_ID,
   a.account_balance AS last_account_balance,
   (
      SELECT Max(account_statement_date)
      FROM accounts a3
      WHERE
         a1.account_ID = a3.account_ID
         AND account_statement_date IS NOT NULL
   ) AS last_statement_date
FROM
   (
      SELECT
         row_number() OVER (PARTITION BY account_ID ORDER BY account_balance_date DESC) AS latest,
         account_ID,
         account_balance_date,
         account_statement_date,
         account_balance
      FROM
         accounts
   ) AS a
 WHERE
    a.latest = 1 -- the most recent balance by account_balance_date
    AND a.account_balance >= 50
    AND ( -- this clause is optional and may aid or harm performance
       a.account_statement_date IS NULL
       OR a.account_statement_date < sysdate - 30
    )
    AND NOT EXISTS ( -- no statement in the last 30 days
       SELECT *
       FROM
          accounts AS a2
       WHERE
          a.account_ID = a2.accountID
          AND a2.account_statement_date >= sysdate - 30
    )
;

I'm suspicious of the 30 days part--it may be that the right calculation will involve some date math about "one month" from the previous statement date, with known rules for deciding what that means for months with a different number of days.

Upvotes: 1

David Faber
David Faber

Reputation: 12485

I'm not sure you should be summing the account balance in aggregate.

SELECT * FROM (
    SELECT account_id, account_balance, account_statement_date
         , MAX(account_statement_date) OVER ( PARTITION BY account_id ) AS max_statement_date
      FROM accounts
) WHERE account_balance > 50
    AND account_statement_date = max_statement_date
    AND max_statement_date < TRUNC(SYSDATE-30);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270311

Just add a having clause to your query:

select account_ID, sum(account_balance)
from accounts
where account_balance > 50
group by account_ID
having max(account_statement_date) < sysdate - 30;

EDIT:

Shankar's comment would seem to be correct. The following fixes it:

select account_ID,
        sum(case when account_balance > 50 then account_balance else 0 end)
from accounts
group by account_ID
having max(account_statement_date) < sysdate - 30;

Upvotes: 1

Shankar
Shankar

Reputation: 879

I think this should work for you.. 

select * from
(select account_ID, sum(account_balance) bal, max(account_statement_date) st_date
from accounts
group by account_ID) tab1
where tab1.bal > 50 and tab1.st_date > (sysdate - 30)

Upvotes: 0

Related Questions