CODEWITHSUNDEEP
sqlsql-server
hasibuddinahmed
hasibuddinahmed

Reputation: 35

Finding values which is both present and not present as per conditions (SQL Server)

I am pretty new to SQL. I have a 'Transactions' table with columns 'Model_Id' and 'Tran_date'. I have another 'Model' table with columns 'Model_Id' and 'Model_Name'. I want all the model names from 'Transactions' table which were sold in the year 2017 but not in the year 2018. How can I use the Where statement to get the required values in SQL Server?

Upvotes: 0

Views: 35

Answers (2)

Thom A
Thom A

Reputation: 95544

Welcome to Stack Overflow. Although I have supplied an answer here (in the form of pseudo-SQL) when posting a question like this you really need to supply Sample data and expected results as a bare minimum. Otherwise volunteers here have to guess your DDL and data, which is probably mean it'll be wrong (but possibly helpful).

As I said above, however, this is pseudo-SQL, but should help you:

SELECT YourColumns
FROM Model M
WHERE EXISTS (SELECT 1
              FROM [Transaction] T
              WHERE T.ModelID = M.ModelID
                AND T.TranactionDate >= '20170101'
                AND T.TranactionDate < '20180101')
  AND NOT EXISTS (SELECT 1
                  FROM [Transaction] T
                  WHERE T.ModelID = M.ModelID
                    AND T.TranactionDate >= '20180101'
                    AND T.TranactionDate < '20190101');

Upvotes: 2

Mani Deep
Mani Deep

Reputation: 1356

Hope this helps!

SELECT m.Model_Name
FROM Transactions t
JOIN Model m on m.model_id=t.model_id
    and year(t.tran_date)=2017
LEFT JOIN Model mm on mm.model_id=t.model_id
    and year(t.tran_date)=2018
WHERE mm.model_id is null

Upvotes: 0

Related Questions