Thank you for the reply. I will walk through the whole process. First of all, the begindate2/enddate2 do not change for any of the ID values because it's the standard time range that I am interested in. Begindate/enddate are the time range for a product that each person owned, they might own it for different time range. What I want to do is to find out, during the begindate2/enddate2, are there any time gaps for Begindate/enddate.
I will take ID 2 as example to demonstrate the process:
1, we use begindate2/enddate2 as the standard time range, which is from 7/12/2020 to 8/1/2021.
2, the first ID 2's Begindate/enddate are 6/20/2020 to 7/20/2020, 6/20/2020 is before 7/12/2020, and 7/20/2020 is before 8/1/2021, so there is no gap.
3, the second ID 2's Begindate/enddate are 9/1/2020 to 11/13/2020, compare to the first ID 2's Begindate/enddate, we notice that the enddate is 7/20/2020, but the begindate is 9/1/2020, so we compute the gap of days are (9/1/2020-7/20/2020) = 41.
4, the third ID 2's Begindate/enddate are 1/15/2021 to 7/21/2021, we noticed two time gap: the first gap is (1/15/2021-11/13/2020)= 53, and the second gap is (8/1/2021[enddate2]-7/21/2021)=9.
5. the total time gap within the range 7/12/2020 to 8/1/2021 are 41+53+9=103.
That's pretty much what I thought the process might be. And the reason that I mention the time order is because I notice that some enddate are later than the next begindate, I used to try the lag(enddate) , because the enddate are later than the next begindate, I don't think that statement works for this kind of situation?
Yeah, the CATE $ doesn't matter here, so I have removed it in the post. Thank you for taking the time!
... View more