0

I have a Customer table

+--------+---------+
| Id     |  Name   |
+--------+---------+
| 1      |   A     |
| 2      |   b     |
| 3      |   c     |
| 4      |   d     |
| 5      |   3     |
| 6      |   f     |
| 7      |   g     |
+--------+---------+

and an order table

+-----+------+--------------------------+
| ID  | C_Id | OrderDate                |
+-----+------+--------------------------+
| 1   | 1    |  2017-05-12 00:00:00.000 |
| 2   | 2    |  2017-12-12 00:00:00.000 |
| 3   | 3    |  2017-11-12 00:00:00.000 |
| 4   | 4    |  2017-12-12 00:00:00.000 |
| 5   | 1    |  2017-12-12 00:00:00.000 |
| 6   | 2    |  2017-12-12 00:00:00.000 |
| 7   | 3    |  2017-12-12 00:00:00.000 |
| 8   | 4    |  2017-11-12 00:00:00.000 |
| 9   | 2    |  2017-06-12 00:00:00.000 |
| 10  | 3    |  2017-07-12 00:00:00.000 |
+-----+------+--------------------------+

I need the result of orders with customers who did not buy in last month. That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 and 4 even they had purchase in earlier months.

I have this query which returns the result perfectly.

SELECT C_ID , MONTH(OrderDate) from [Order] WHERE MONTH(OrderDate) <> MONTH(GETDATE()) - 1 AND C_ID NOT IN (SELECT C_ID FROM [Order] WHERE MONTH(OrderDate) = MONTH(GETDATE()) - 1)

Can anyone help me to write this query without using subquery

For better clarity, assume the Orders table contains current year orders only and I need to exclude the customers from the result(get all orders for current year) if they had any purchase in November. Which means if the customer John has an order in April, June and November the result(orders with customer name) should not include the customer John and all his orders as he made a purchase in November..

For learning purpose I need to know how can we achieve the above in other ways without using sub query.

1
  • The MONTH(o.OrderDate) = MONTH(GETDATE()) - 1 is not entirely correct. It does not take Year into account. Dec 27, 2017 at 13:22

1 Answer 1

2

I need the result of the customers who did not buy in last month.

That is from the order table Customer 3 and 4 have purchased in last month(November). The result should not include customer 3 and 4 even they had purchase in earlier months.

I am a bit confused with your question.

If you want to see customers who did not purchase last month (in this case, November) Then below query should be able to handle it.

SELECT DISTINCT
    c.id,
    c.name 
FROM #Customer c
LEFT JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1
WHERE o.c_id IS NULL

If you want to see customers who only purchased last month (in this case, November) Then below query should be able to handle it.

 SELECT DISTINCT
        c.id,
        c.name 
    FROM #Customer c
    INNER JOIN #Orders o ON c.id = o.c_id AND MONTH(o.OrderDate) = MONTH(GETDATE()) - 1
4
  • thanks for your time. I updated the question ie. I need the result of orders with customers who did not buy in last month. For example, I need to get all orders. If the customer John has an order in April, June and November the result(orders with customer name) should not include the customer John and all his orders as he made a purchase in November. Dec 27, 2017 at 12:22
  • The subquery mentioned in the question returns as expected, but I need to learn is there any other way to achieve this without using subquery ? Dec 27, 2017 at 12:23
  • 1
    The MONTH(o.OrderDate) = MONTH(GETDATE()) - 1 is not entirely correct. It does not take Year into account. Dec 27, 2017 at 13:21
  • yup. I just rushed a bit to answer his question. I am just a newbie. But with that script, he can use it as the basis for his needs. Dec 28, 2017 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.