SQL Puzzle | The OrdersAndState puzzle

We need to find out customers who have placed orders in 2017 and for those orders the amount should be equal or greater than $100.
Note that if it has multiple orders all must be at least $100 and the order must be going to IL. Also If there are multiple orders that meet 1 and 2 then all must be going to IL

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

>

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE OrderAndStates
(
	 Customerid INT
	,OrderDate DATETIME 
	,[State] VARCHAR(2)    
	,Orderamount INT
)
GO

INSERT INTO OrderAndStates VALUES
(1,'11/02/2016','IL',100),
(2,'02/05/2017','IL',200),
(3,'06/05/2017','WI',200),
(3,'05/15/2017','WI',200),
(1,'03/02/2017','IL',100)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--
SELECT b.*
FROM OrderAndStates b
CROSS APPLY 
( 
	SELECT 1 r FROM OrderAndStates a 
	WHERE a.Customerid = b.Customerid 
	AND (Orderamount) >= 100
	AND State = 'IL' 				
	HAVING MIN(orderdate) >= '20170101'
)r	
--

Output

/*------------------------
SELECT b.*
FROM OrderAndStates b
CROSS APPLY 
( 
	SELECT 1 r FROM OrderAndStates a 
	WHERE a.Customerid = b.Customerid 
	AND (Orderamount) >= 100
	AND State = 'IL' 				
	HAVING MIN(orderdate) >= '20170101'
)r
------------------------*/
Customerid  OrderDate               State Orderamount
----------- ----------------------- ----- -----------
2           2017-02-05 00:00:00.000 IL    200

(1 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com