CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user4861279
user4861279

Reputation: 53

Get users which have orders in one row but doesn't have in another SQL Server 2012

I have one table shown in the image below. I need to get all users which have an order in February but don't have an order in March.

I have the following query but I don't have an idea how to finish it.

SELECT *
FROM My_Table
WHERE Order > 0
    AND Date BETWEEN '20150201' AND '20150228' 

User       Date         order
-----------------------------
Aleks     2015-02-01    100
Tatya     2015-03-10    200
Sabine    2015-02-08    300
Aleks     2015-03-27    150

Upvotes: 1

Views: 51

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Assuming you only want each user once:

SELECT User
FROM My_Table
WHERE
  Order > 0
  AND Date BETWEEN '20150201' AND '20150331' 
GROUP BY User
HAVING max(date) < '20150301'

Upvotes: 0

potashin
potashin

Reputation: 44581

You can use not exists with the correlated subquery:

select * 
from [My_Table] t
where [Order] > 0
  and ([Date] between '20150201' and '20150228')
  and not exists ( select *
                   from [My_Table]
                   where [Order] > 0
                     and [User] = t.[User]
                     and [Date] between '20150301' and '20150331')

Upvotes: 3

Related Questions