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'),
(1,'2011-02-05','e2'),
(1,'2012-01-11','e3'),
(7,'2009-05-13','e1'),
(7,'2009-09-18','e2'),
(7,'2010-01-14','e3'),
(7,'2010-05-07','e4'),
(7,'2011-02-23','e5'),
(7,'2012-05-15','e6'),
(7,'2013-05-08','e7'),
(7,'2013-06-15','e8'),
(7,'2013-06-22','e9'),
(19,'2013-02-12','e1'),
(20,'2011-12-05','e1'),
(29,'2009-05-16','e1'),
(37,'2009-05-11','e1'),
(37,'2009-05-18','e2'),
(61,'2009-07-13','e1'),
(84,'2013-09-14','e1'),
(84,'2009-09-24','e2') 

create table tableB (PersonID int, FollowupDate date)
Insert into tableB values(1,'2010-09-09')
,(1,'2012-03-19')
,(1,'2012-06-01')
,(7,'2009-10-10')
,(7,'2009-11-18')
,(7,'2010-01-18')
,(7,'2011-02-28')
,(7,'2013-06-19')
,(7,'2013-06-26')
,(7,'2013-06-28')
,(19,'2013-04-11')
,(20,'2012-02-01')
,(37,'2009-07-03')
,(37,'2009-08-05')
,(61,'2009-12-12')
,(84,'2009-09-17')
,(84,'2009-09-29')

;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

 

https://social.msdn.microsoft.com/Forums/en-US/a388d555-26a3-4bd7-9b8c-2ea2d2f3551e/calculating-date-difference-for-multiple-events-per-person?forum=transactsql

Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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