Using Snowflake, I am attempting to subset on customers that have no current subscriptions, and eliminating all IDs for those which have current/active contracts.
Each ID will typically have multiple records associated with a contract/renewal history for a particular ID/customer.
It is only known if a customer is active if there is no contract that goes beyond the current date, while there are likely multiple past contracts which have lapsed, but the account is still active if one of those contract end dates goes beyond the current date.
Consider the following table:
| Date_Start | Date_End | Name | ID |
|---|---|---|---|
| 2015-07-03 | 2019-07-03 | Piggly | 001 |
| 2019-07-04 | 2025-07-04 | Piggly | 001 |
| 2013-10-01 | 2017-12-31 | Doggy | 031 |
| 2018-01-01 | 2018-06-30 | Doggy | 031 |
| 2020-01-01 | 2021-03-14 | Catty | 022 |
| 2021-03-15 | 2024-06-01 | Catty | 022 |
| 1999-06-01 | 2021-06-01 | Horsey | 052 |
| 2021-06-02 | 2022-01-01 | Horsey | 052 |
| 2022-01-02 | 2022-07-04 | Horsey | 052 |
With a desired output non-active customers that do not have an end date beyond Jan 5th 2023 (or current/arbitrary date)
| Name | ID |
|---|---|
| Doggy | 031 |
| Horsey | 052 |
My first attempt was:
SELECT Name, ID
FROM table
WHERE Date_End < GETDATE()
but the obvious problem is that I'll also be selecting past contracts of customers who haven't expired/churned and who have a contract that goes beyond the current date.
How do I resolve this?
CodePudding user response：
You can work it out with an EXCEPT operator, if your DBMS supports it:
SELECT DISTINCT Name, ID FROM tab
EXCEPT
SELECT DISTINCT Name, ID FROM tab WHERE Date_end > <your_date>
This would removes the active <Name, ID> pairs from the whole.
CodePudding user response：
As there are many rows per name and ID, you should aggregate the data and then use a HAVING clause to select only those you are interested in.
SELECT name, id
FROM table
GROUP BY name, id
HAVING MAX(date_end) < GETDATE();