SQL query to find Old clients that have transactions before 2014 but nothing afterwards?

Self-taught beginner here. I want to sort out (old) clients that have last transactions in 2014 but nothing else till date.

I tried this:

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')

but get no result

THEN I tried sorting with excel but even more difficult

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

I expect the output to be

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

If you want clients whose last trade is in 2014, then use aggregation:

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';

If you want information about the last transaction for such accounts, use window functions:

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';

Advertisement

Answer