customers who were active in the last to last month, but inactive in last month in SQL server

Question

I need to get the customers who were active in the may month, but inactive in June month in SQL server .

Here is my query :

SELECT DISTINCT c.CustomerId,
                c.CustomerCode
FROM Customer.Customer(nolock) c
LEFT JOIN Customer.Card (nolock)cd ON c.CustomerId=cd.CustomerId
AND CD.Status NOT IN (6,
                      8)
LEFT JOIN Trans.RawTransaction rt (nolock) ON rt.AccountNumber=cd.CardNumber
AND rt.AccountTypeId=3
AND rt.TransactionDate>='01-05-2014'
AND rt.TransactionDate<'01-06-2015'
WHERE NOT EXISTS
    (SELECT ca.customercode
     FROM Customer.customer Ca (nolock)
     INNER JOIN Customer.Card cd (nolock) ON ca.CustomerId=cd.CustomerId
     INNER JOIN trans.vwValidRawTransactions ra (nolock) ON cd.CardNumber=ra.AccountNumber
     AND ra.AccountTypeId=3
     AND ra.IsLive=1
     WHERE Ra.TransactionDate>='01-06-2015'
       AND ra.TransactionDate<'01-07-2015'
       AND ca.CustomerCode=C.CustomerCode)


Please help..

and what is not working? Can you add a few rows of test data and its current outcome, maybe by creating an [sqlfiddle](http://sqlfiddle.com/) — rene, Jul 06 '15 at 08:48
If you have taken TransactionDate as datetime you can't use this as varchar datatype.you have to cast this. — Sabyasachi Mishra, Jul 06 '15 at 08:50
Don't re-use cd as table alias. Very confusing for everybody! — jarlh, Jul 06 '15 at 08:57

score 0 · Answer 1 · edited May 23 '17 at 11:58

try this...! Instead using Not Exists use NOT IN

SELECT DISTINCT c.CustomerId,
                c.CustomerCode
FROM Customer.Customer(nolock) c
LEFT JOIN Customer.Card (nolock)cd ON c.CustomerId=cd.CustomerId
AND CD.Status NOT IN (6,
                      8)
LEFT JOIN Trans.RawTransaction rt (nolock) ON rt.AccountNumber=cd.CardNumber
AND rt.AccountTypeId=3
AND rt.TransactionDate>='01-05-2014'
AND rt.TransactionDate<'01-06-2015'
WHERE c.CustomerCode NOT IN
    (SELECT ca.customercode
     FROM Customer.customer Ca (nolock)
     INNER JOIN Customer.Card cd (nolock) ON ca.CustomerId=cd.CustomerId
     INNER JOIN trans.vwValidRawTransactions ra (nolock) ON cd.CardNumber=ra.AccountNumber
     AND ra.AccountTypeId=3
     AND ra.IsLive=1
     WHERE Ra.TransactionDate>='01-06-2015'
       AND ra.TransactionDate<'01-07-2015'
       AND ca.CustomerCode=C.CustomerCode)

Source: NOT IN vs NOT EXISTS

customers who were active in the last to last month, but inactive in last month in SQL server

1 Answers1