I have a table that is structured like the below - this contains details about all customer subscriptions and when they start/end.

I am trying to add an indicator flag to this table (either "yes" or "no") which shows me by each row, if when the [EndDate] of the SubKey is the last one for that CustomerID. So for the above example..

The flag is set to No for the first row, because on 1 Jan 2014, customerID 102 had another SubKey (29334) still active at the time (which didn't end until 15 jun 2022)

The rest of the rows are set to "Yes" because these were the last active subscriptions per CustomerID.

I have been reading about the LAG function which may be able to help. I am just not sure how to make it fit in this scenario.

CodePudding user response：

Probably the easiest method would to use exists with a correlation. Can you try the following for your desired results for excluding rows without an overlap:

select *, 
  case when exists (
    select * from t t2 
    where t2.customerId = t.customerId 
      and t2.enddate > t.enddate 
      and t2.startDate < t.Enddate
  ) then 'No' else 'Yes' end as IsLast
from t;