I have a table with customer names, items and dates added
| ID | Cust_ID | Item | DateAdded |
|---|---|---|---|
| 1 | Cust_1 | Handle | 2022-12-05 11:51:28.973 |
| 2 | Cust_1 | Foot | 2022-12-02 14:43:36.407 |
| 3 | Cust_1 | Door | 2022-12-02 14:42:20.727 |
| 4 | Cust_2 | Handle | 2022-10-10 13:07:49.640 |
| 5 | Cust_2 | Door | 2022-09-15 12:09:13.820 |
| 6 | Cust_2 | Leg | 2022-12-02 11:02:43.110 |
| 7 | Cust_3 | Handle | 2022-07-01 15:31:28.547 |
| 8 | Cust_3 | Door | 2022-12-06 10:26:56.987 |
I need a select statement that returns the customer name but only where all items purchased were last month. Example, all purchases for Cust_1 were last month so this customer is returned but Cust2 and Cust_3 had purchases in other months so they are not returned.
| Cust_ID |
|---|
| Cust_1 |
I have the date range sorted out and have tried various 'Group By' and 'Having' clauses but im struggling due to it being dates and not strings.
CodePudding user response：
Use a HAVING clause with MIN and MAX on your DAteAdded Column. You can easily create a date boundary with DATEADD and EOMONTH:
SELECT Cust_ID
FROM dbo.YourTable
GROUP BY Cust_ID
HAVING MIN(DateAdded) >= DATEADD(DAY, 1, EOMONTH(GETDATE(),-2))
AND MAX(DateAdded) < DATEADD(DAY, 1, EOMONTH(GETDATE(),-1));