CODEWITHSUNDEEP
sqlpostgresql
KMD
KMD

Reputation: 101

Query on same column with different condition - groups

I have a transaction table like this :

  acct_id      trans_type         date_of_tran
-------------------------------------------------
     A          WITHDRAW          2021-Jun-20
     A          DEPOSIT           2021-Jun-21
     A          DEPOSIT           2021-Jun-22 

     B          WITHDRAW          2021-Jun-20
     B          WITHDRAW          2021-Jun-21

     C          DEPOSIT           2021-Jun-20
     C          DEPOSIT           2021-Jun-21
     C          WITHDRAW          2021-Jun-21
     C          WITHDRAW          2021-Jun-21

I want to find out which accounts have only WITHDRAWN money but have not deposited any within a specific time duration .

Example - Account B has only withdrawn money - no deposite within Jun20-21

strong text

Upvotes: 1

Views: 67

Answers (4)

shikida
shikida

Reputation: 505

this may not be elegant, but at least it's easy to understand :-)

SELECT aa
FROM   t
WHERE  cc < '2021-Jun-22'
       AND cc > '2021-Jun-19'
GROUP  BY aa,
          bb
HAVING bb = 'WITHDRAW'
EXCEPT
SELECT aa
FROM   t
WHERE  cc < '2021-Jun-22'
       AND cc > '2021-Jun-19'
GROUP  BY aa,
          bb
HAVING bb = 'DEPOSIT'

where

aa = acct_id      
bb = trans_type         
cc = date_of_tran

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use aggregation, like this:

select acct_id
from transactions
where date_of_tran >= ? and date_of_tran < ?
group by acct_id
having min(trans_type) = 'WITHDRAW';

This uses the fact that 'DEPOSIT' < 'WITHDRAW'.

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

You can count the number of rows per account id and compare it to the number of WITHDRAW lines. If they're the same, the account has only withdrawals

SELECT acct_id 
FROM transaction
WHERE date_of_tran BETWEEN '2021-06-20' AND '2021-06-21'
GROUP BY acct_id
HAVING COUNT(*) = COUNT(CASE WHEN trans_type = 'WITHDRAW' THEN 1 END)

An SQLfiddle to test with.

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

You can select all the rows with trans_type 'WITHDRAW' from transaction_table then ignore the accounts for which DEPOSIT transaction is available with the help of not exists

select * from transaction_table  tt
where trans_type='WITHDRAW'
and not exists
              (
                  select 1 from transaction_table tt1 
                  where tt.acct_id=tt1.acct_id and trans_type='DEPOSIT'
                  and date_of_tran >= '2021-Jun-20' and 2021-Jun-20<='2021-Jun-21'
              );

Upvotes: 1

Related Questions