带有 WHERE 和 HAVING 子句的最大日期 - Teradata

发布于 2024-12-28 07:31:17 字数 435 浏览 0 评论 0原文

我有一个包含 acc_nbr、tran_nbr、tran_amt、tran_dt 和 tran_typ 的事务表。

我只需要找到 acc_nbr，其中 tran_typ = 'xx' 且 sum(tran_amt)<0 并显示最新的 tran_dt。

我可以找到“xx”tran_typ 总和小于零的所有帐户，但如果不添加所有包含负 tran_typ“xx”的交易，我似乎无法获取其中的日期。

目前有效的方法：

    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

如何输入最新交易的日期？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

顾冷 2025-01-04 07:31:17

最好的选择是在一组中找到所有负总和的帐户，在另一组中找到每个帐户的最新交易日期，并将它们内部连接到同一帐户上。

您可能想尝试以下操作：

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

teradata 的语法可能略有不同，但总体思路应该是相同的。

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.

回复收藏 0 原文

小苏打饼 2025-01-04 07:31:17

我不太确定我理解这个问题，但你应该能够在选择中添加一个 MAX(trans_dt) ，它会给你最近的交易日期

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

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

回复收藏 0 原文

~没有更多了~