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

Customerid OrderDate State Orderamount
1 2016-11-02 00:00:00.000 IL 100
2 2017-02-05 00:00:00.000 IL 200
3 2017-06-05 00:00:00.000 WI 200
3 2017-05-15 00:00:00.000 WI 200
1 2017-03-02 00:00:00.000 IL 100

Expected Output

Customerid OrderDate State Orderamount
2 2017-02-05 00:00:00.000 IL 200

>

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”.

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