SQL查询以查找2014年之前有交易的旧客户，但此后什么都没有

Question

在这里自学成才的初学者。我想整理(旧的(在2014年进行最后交易的(旧的(客户，但迄今为止什么都没有。

我尝试了：

select distinct transact_clntab.chn, transact_clntab.TRADE_DATE, transact_clntab.acct_code from  transact_clntab
WHERE Trade_date not between '2019-01-01' and '2019-12-31'
and TRADE_DATE not between '2018-01-01' and '2018-12-31'
and TRADE_DATE not between '2017-01-01' and '2017-12-31'
and TRADE_DATE not between '2016-01-01' and '2016-12-31'
and TRADE_DATE not between '2015-01-01' and '2015-12-31'
and not exists
(SELECT chn, TRADE_DATE, acct_code FROM transact_clntab 
WHERE Trade_date between '2008-01-01' and '2019-12-31'
and TRADE_DATE between '2009-01-01' and '2018-12-31'
and TRADE_DATE between '2010-01-01' and '2017-12-31'
and TRADE_DATE between '2011-01-01' and '2016-12-31'
and TRADE_DATE between '2012-01-01' and '2015-12-31'
and TRADE_DATE between '2013-01-01' and '2017-12-31'
and TRADE_DATE between '2014-01-01' and '2016-12-31')

但没有结果

然后我尝试使用Excel进行分类，但更困难

SELECT chn, TRANTYPE, ACCT_CODE, TRADE_DATE FROM transact_clntab 
where TRADE_DATE < '2016-01-01'
order by TRADE_DATE

我希望输出为

chn      Transactiontype Account code   Trade date
7683592  SALES           BABA-2688  2008-06-02 00:00:00.000
58987897 PURCHASE        FASH-1492  2008-06-02 00:00:00.000

Answer 1

如果您想要2014年的最后交易客户，请使用聚合：

SELECT t.acct_code
FROM transact_clntab t
GROUP BY t.acct_code
HAVING MAX(t.trade_date) < '2015-01-01' AND
       MAX(t.trade_date) >= '2014-01-01';

如果您需要有关此类帐户的最后一次交易的信息，请使用窗口功能：

SELECT t.*
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY t.acct_code ORDER BY t.trade_date DESC) as seqnum
      FROM transact_clntab t
     ) t
WHERE seqnum = 1 AND
      t.trade_date < '2015-01-01' AND
      t.trade_date >= '2014-01-01';

Answer 2

尝试通过客户端ID(我认为它是CHN(并获取最大日期，并将日期小于2015年的条件分组。

。

SELECT transact_clntab.chn, MAX(transact_clntab.TRADE_DATE) last_transaction, transact_clntab.acct_code
FROM transact_clntab
GROUP BY transact_clntab.chn,transact_clntab.acct_code
HAVING last_transaction < '2015-01-01';

希望这会有所帮助！

Answer 3

你很接近。但是，您没有与客户相关联，这就是为什么NOT EXISTS的子查询总是发现一些行。您的多个 BETWEEN s也可以简化，并且可能会更好地被右开机间隔的检查替换。

假设acct_code标识客户，您可以尝试：

SELECT DISTINCT
       tc1.chn,
       tc1.trade_date,
       tc1.acct_code
       FROM transact_clntab tc1
       WHERE tc1.trade_date >= '2014-01-01'
             AND tc1.trade_date < '2015-01-01'
             AND NOT EXISTS (SELECT *
                                    FROM transact_clntab tc2
                                    WHERE tc2.acct_code = tc1.acct_code
                                          AND tc2.trade_date >= '2015-01-01');