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.

  • The MONTH(o.OrderDate) = MONTH(GETDATE()) - 1 is not entirely correct. It does not take Year into account. – ypercubeᵀᴹ Dec 27 '17 at 13:22
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
  • 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. – Manikandan Sekar Dec 27 '17 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 ? – Manikandan Sekar Dec 27 '17 at 12:23
  • 1
    The MONTH(o.OrderDate) = MONTH(GETDATE()) - 1 is not entirely correct. It does not take Year into account. – ypercubeᵀᴹ Dec 27 '17 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. – Edgar Allan Bayron Dec 28 '17 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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