Multiple filter in multiple Column in sql-CodePudding

I have one table with three columns as User_ID, order_status and Date. Order status is success and 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：

In SQL Server you can do:

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'

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