Calculating Date Difference for Multiple Events


  create table tableA (PersonID int, EventDate date, EventLabel varchar(10))

Insert into tableA values(1,'2010-07-17','e1'),

create table tableB (PersonID int, FollowupDate date)
Insert into tableB values(1,'2010-09-09')

;With mycte as (
select PersonID, EventDate, EventLabel , 1 as grp from tableA
Union all
select PersonID, FollowupDate,
'e'+ Cast(row_number() Over(Partition by PersonID Order by FollowupDate) as varchar(10)) +'-followup' as EventLabel
, 0 as grp from tableB

,mycte1 as (
Select *, sum(grp) Over(Partition by PersonID Order by EventDate) grp2 from mycte )

,mycte2 as (
Select PersonID, min(EventDate) Over(Partition by PersonID, grp2) as EventDate
--, min(EventDate) Over(Partition by PersonID, grp2,grp) minFollowupDate
,datediff(day, min(EventDate) Over(Partition by PersonID, grp2),min(EventDate) Over(Partition by PersonID, grp2,grp)) waitDate
,row_number() Over(Partition by PersonID, grp2 Order by grp) rn
From mycte1

Select PersonID,EventDate, waitDate from mycte2
Where rn=1
order by PersonID,EventDate
Drop table   tableA, tableB

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s