Home > Blockchain >  Subsetting on dates for a SQL query
Subsetting on dates for a SQL query

Time:01-07

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();
  • Related