Having case multiple conditions in SQL Server 2014

Question

I have a table 'FinancialTrans' where only 3 of those fields are needed.

AcctID  TransTypeCode   DateOfTrans  Field 4  Field 5  Field 6....
123     TOLL            2016-06-06
123     TOLL            2016-06-02
123     TOLL            2016-04-28
123     PYMT            2016-03-11
123     TOLL            2015-12-22
123     TOLL            2015-12-22

What I need:
I only need account numbers where there are No Tolls AND No Pymt in the last 2 years.

My attempt at the code:
I know I need a Having clause but not quite sure how to write it.
Perhaps, a NOT Exist?

SELECT [AcctID]
      ,[TransTypeCode]
      ,[TransDate]

 FROM [FinancialTrans]

 WHERE (
        (TransTypeCode = 'TOLL' AND Max(TransDate) <= DATEADD(year, -2, GETDATE()))
     OR (TransTypeCode = 'PYMT' AND Max(TransDate) <= DATEADD(year, -2, GETDATE()))
       )

 GROUP BY AcctID, TransTypeCode, TransDate

The challenge I'm facing is that I want account numbers where there is NEITHER a toll NOR a payment in the past two years. I'm getting account numbers that have no tolls in the past two years but has a payment in the past two years.

Question: How do I ensure I get account numbers that doesn't have BOTH in the past two years? This question is different from an earlier question asked because the requirements have now changed.

Do you have an accounts table? All accounts that don't have entrees in this table requires a list of all accounts and not just the table that has the exclusions. — Twelfth, Nov 15 '17 at 19:09
Yes. I have an accounts table. But that doesn't have data that the FinanceTrans table holds. __ I was hoping that once I get a list of accounts that don't have TOLL or PYMT entries for the past two years, I can only run data for Account Numbers from this temp table. — user1777929, Nov 15 '17 at 19:12
Looks to me you are using SO as a free workforce. You say requirement change? More like you don't know what you need. Because you didnt wait 30 minutes to ask again. And you didn't provide feedback or accept any of the answer of the other question, you just ignore it. So that is ungrateful with the people spending time with you. And If you spend some time trying to understand the answers on those question you could learn something already but I guess is easy keep asking other do the job for you. — Juan Carlos Oropeza, Nov 15 '17 at 20:16
It would have been considered as a free workforce if I didn't try anything at all. But I've tried all the suggested codes but none worked. I guess the logic I am applying is wrong. I'm just going to try something else. Thanks for your help anyway. — user1777929, Nov 15 '17 at 20:58
I've found a solution to my problem. Please see the other question page for how I resolved it. — user1777929, Nov 16 '17 at 22:47

score 0 · Answer 1 · edited Nov 15 '17 at 20:32

0

You can use group by and having:

SELECT [AcctID]
FROM [FinancialTrans]
GROUP BY [AcctID]
HAVING MAX(CASE WHEN TransTypeCode = 'TOLL' THEN TransDate END) <= DATEADD(year, -2, GETDATE()) AND
       MAX(CASE WHEN TransTypeCode = 'PYMT' THEN TransDate END) <= DATEADD(year, -2, GETDATE()) ;

That above actually requires that there be both types of transactions. It might be better to do:

SELECT [AcctID]
FROM [FinancialTrans]
GROUP BY [AcctID]
HAVING SUM(CASE WHEN TransTypeCode IN ('TOLL', 'PYMT') AND TransDate > DATEADD(year, -2, GETDATE())
                THEN 1 ELSE 0
           END) = 0;

edited Nov 15 '17 at 20:32

user1777929

777
1
10
28

answered Nov 15 '17 at 19:10

Gordon Linoff

1,242,037
58
646
786

I tried your second code but that gives me records with other TransTypeCodes as well. Also, the years are from last year and older years too. – user1777929 Nov 15 '17 at 19:30

score 0 · Answer 2 · answered Nov 15 '17 at 19:22

Not exists would work also.

Select  AcctID,
        TransTypeCode,
        TransDate
From    FinancialTrans ft1
Where   Not Exists (Select  1 
                    From    FinancialTrans ft2 
                    Where   ft1.AcctID = ft2.AcctID 
                    and     ft2.TransTypeCode IN ('TOLL','PYMT') 
                    and     ft2.DateOfTrans > DATEADD(year, -2, getdate()))

Having case multiple conditions in SQL Server 2014

2 Answers2