Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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;

jflanner

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