CODEWITHSUNDEEP
sqlteradata
Donna Panzarotti
Donna Panzarotti

Reputation: 63

Max Date with WHERE and HAVING clauses- Teradata

I have a single transaction table with acc_nbr, tran_nbr, tran_amt, tran_dt, and tran_typ.

I need to find only acc_nbr where tran_typ = 'xx' and sum(tran_amt)<0 and show the most recent tran_dt.

I can find all accounts where the sum of the 'xx' tran_typ are less than zero, but I can't seem to get the date in there without adding all of the transactions that contain a negative tran_typ 'xx'.

What works so far:

    SEL acc_nbr, SUM(tran_amount) as error
    FROM DATABASE.TRAN_TBL

    WHERE tran_typ = 'xx' 
    GROUP BY acc_nbr
    HAVING sum(tran_amt)<0

How can I bring in the date of the latest transaction?

Upvotes: 2

Views: 24771

Answers (2)

piotrm
piotrm

Reputation: 12356

The best bet would be to find all accounts with negative sum in one set, latest date of transaction for each account in another set and inner join them both together on the same account.

You may want to try something like:

SEL sumtran.acc_nbr, sumtran.error, latest.latest_dt 
FROM 
  ( SEL acc_nbr, SUM(tran_amt) AS error
    FROM DATABASE.TRAN_TBL
    WHERE tran_typ = 'xx' 
    GROUP BY acc_nbr
    HAVING sum(tran_amt)<0 ) AS sumtran
INNER JOIN
  ( SEL acc_nbr, MAX(tran_dt) AS latest_dt
    FROM DATABASE.TRAN_TBL
    GROUP BY acc_nbr ) AS latest
ON sumtran.acc_nbr = latest.acc_nbr

Syntax for teradata might be slightly different but the overall idea should be the same.

Upvotes: 4

jakraska
jakraska

Reputation: 749

I'm not quite sure i understand the problem, but you should just be able to add a MAX(trans_dt) to the select and it will give you the more recent transaction date

SEL acc_nbr, SUM(tran_amount) as error, MAX(tran_dt) as most_recent_dt
FROM DATABASE.TRAN_TBL

WHERE tran_typ = 'xx' 
GROUP BY acc_nbr
HAVING sum(tran_amt)<0

Upvotes: 0

Related Questions