How to retrieve date for main ID depending on absence previously occurring id in other column?

Transact-SQL
1

Hi developers,

I do have a question I can't resolve... Maybe there's someone who can help me on this one?

I have a table with data regarding persons who have subscribed to something. In most cases, there are two people who have subscriptions under the same subscription_id. In some cases (see example) one of the persons stops the subscription earlier than the second person.

What I would like is an overview of the date when the person who stops the subscription first, the subscription_id and the person_nr. Or, in case of only one subscriber, the same information but for this one person.

My sample data:

Drop table b;
create table B (reference_date date, subscription_id int, person_nr int)
Insert into B values ('2022-01-31',1000,15)
Insert into B values ('2022-01-31',1000,16)
Insert into B values ('2022-02-28',1000,15)
Insert into B values ('2022-02-28',1000,16)
Insert into B values ('2022-03-31',1000,15)
Insert into B values ('2022-03-31',1000,16)
Insert into B values ('2022-04-30',1000,15)
Insert into B values ('2022-05-31',1000,15)
Insert into B values ('2022-06-30',1000,15)
Insert into B values ('2022-07-31',1000,15)

Insert into B values ('2021-01-31',1001,18)
Insert into B values ('2021-01-31',1001,19)
Insert into B values ('2021-02-28',1001,18)
Insert into B values ('2021-02-28',1001,19)
Insert into B values ('2021-03-31',1001,19)
Insert into B values ('2021-04-30',1001,19)
Insert into B values ('2021-05-31',1001,19)

Insert into B values ('2022-06-30',1002,50)
Insert into B values ('2022-06-30',1002,51)
Insert into B values ('2022-07-31',1002,50)
Insert into B values ('2022-07-31',1002,51)
Insert into B values ('2022-08-31',1002,50)
Insert into B values ('2022-08-31',1002,51)
Insert into B values ('2022-09-30',1002,50)
Insert into B values ('2022-09-30',1002,50)
Insert into B values ('2022-10-31',1002,50)

Insert into B values ('2022-08-31',1003,25)
Insert into B values ('2022-09-30',1003,25)
Insert into B values ('2022-10-31',1003,25)

My desired outcome:
Question_outcome

2

I have no idea which rows start subscrips and which rows stop them. Keep in mind, we know nothing about your data.

3 
WITH MaxDates
AS
(
	SELECT MAX(reference_date) AS reference_date
		,subscription_id, person_nr
	FROM B
	GROUP BY subscription_id, person_nr
)
,RNs
AS
(
	SELECT reference_date, subscription_id, person_nr
		,ROW_NUMBER() OVER (PARTITION BY subscription_id ORDER BY reference_date) AS rn
	FROM MaxDates
)
SELECT reference_date, subscription_id, person_nr
FROM RNs
WHERE rn = 1;

or

WITH MaxDates
AS
(
	SELECT MAX(reference_date) AS reference_date
		,subscription_id, person_nr
		,ROW_NUMBER() OVER (PARTITION BY subscription_id ORDER BY MAX(reference_date)) AS rn
	FROM B
	GROUP BY subscription_id, person_nr
)
SELECT reference_date, subscription_id, person_nr
FROM MaxDates
WHERE rn = 1;
1 Like
4

Hi, thank you for your reply. In the row "Reference_date" the start- and enddate is shown for every subscription_id. A subscription_id is the unique id for every subscription. One or two persons can take a subscription with the same id.

And now the situation is, if there are 2 persons on 1 subscription, and 1 person ends the subscription earlier than the other person, on which date is that?

Just like a married couple, both having the same mortgage loan. Then, they divorce. One person keeps the mortgage loan, and the other person will 'disenroll'. I want to know on what date the person disappears from the table while the 'loan' is still active.

5

No, it doesn't show "start" and "end" dates, just a bunch of "reference_date"s. Good luck with this, hopefully what Ifor did will help you.

6

What Ifor did does help

image
image1244×731 22 KB

7

Ha, both work like a charm! Thank you so much!

8

They work with the given test data but may not with your real data depending on what else is present.

10

Hi, thank you for your reply!

The test data contains, for the present exercise, every possible combination needed. All of this was tested on real data. Unexpected results here are due to very infrequent DQ issues that occur in many DBs. However, these are known, flagged and will be handled otherwise.