Multiple filter in multiple columns in SQL-CodePudding

I have a table with three columns User_ID, order_status and Date. Order_status is success or failure.

I need to find User_ID of those who have placed orders in Jan'21-Mar'21 and Jul'21-Sep'21 and not a successful order in Apr'21-Jun'21.

CodePudding user response：

This statement should work for you:

SELECT *
FROM table
WHERE order_status = 'failure' 
AND (date BETWEEN '2021-01-01' AND '2021-03-31' OR date BETWEEN '2021-07-01' AND '2021-09-30')

CodePudding user response：

SELECT [USER_ID]
,[ORDER_STATUS]
,[Date]
FROM [TBL_MAIN]
WHERE [Date] >= '2021-01-01' AND [Contract Date] < '2021-03-31'
AND ([ORDER_STATUS] = 'Success' AND [Date] >= '2021-04-01' AND [Contract Date] <       '2021-60-31')
ORDER BY [Date] DESC

CodePudding user response：

In SQL Server you can use EXCEPT to find the set difference between two bags of rows:

select user_id 
from t
where order_status = 'success' and date between '2021-01-01' and '2021-03-31'
except 
select user_id 
from t
where order_status = 'failure' and date between '2021-07-01' and '2021-09-30'