Gap analysis of time intervals

Go down

Gap analysis of time intervals Empty Gap analysis of time intervals

Post  jflanner on Mon Nov 04, 2013 10:25 am

I am in the tax advantaged debit card space where people can have multiple products on the debit card.  (Example:  HSA, Child Care FSA, etc.)  These products are not necessarily subscribed to at the same time; or for the same time intervals.

My problem is, from a customer management and invoicing perspective, we care only that you have a subscription.  And that is a relationship that exists over a period of time – hopefully longer then the constituent product subscriptions you have.  (Basically – we want you to renew your products.)

So ….   The code below returns the total time interval we had a relationship with an individual – 20080101 – 20121231.  The problem – there is a gap.  There are no subscriptions in 2011.  I need the code below to return 2 rows:  (20080101, 20101231) and (20120101, 20121231)  There is a DimDate behind these integers.

Any help appreciated.

declare @ProductSubscription table
   SubscriptionStartDate int,
   SubscriptionEndDate int

insert into @ProductSubscription values
   (20080101, 20081231),
   (20080101, 20081231),
   (20080621, 20081231),
   (20090101, 20091231),
   (20100101, 20101231),
   (20120101, 20121231),
   (20120601, 20121031);
select min(SubscriptionStartDate), max(SubscriptionEndDate) from @ProductSubscription;


Posts : 5
Join date : 2012-06-04

Back to top Go down

Back to top

Permissions in this forum:
You cannot reply to topics in this forum