Hi,
I'm trying to count the number of overlapping days based on starting and enddates. See a example below:
I'm thus interested in calculating the overlapping days in the last column, which is thus the sum of all overlapping days of programs of other rows.
A possible way to do this:
1) create dummy variables for all days
2) update dummy variables to 1 when dummy variable lies between startdate and enddate
3) sum values of dummy variable between startdate and enddate
However, I'm not sure if this is the most convenient way to do this.
I already managed to create dummy variables, but am unable to match variable names with values of columns. Furthermore, I have around 6000 rows, and the time span is more than 4000 days, so I want to prevent looping over both rows and columns for step 2/3.
Does anyone know a good solution for this?
With kind regards,
Nick
I'm trying to count the number of overlapping days based on starting and enddates. See a example below:
id | startdate | enddate | overlapping days |
1 | 01-01-2004 | 01-01-2005 | 31 (first month of program 2) |
2 | 01-12-2004 | 01-03-2005 | 90 (last month of program 1 + first two months of program 3) |
3 | 01-01-2005 | 01-04-2005 | 59 (last two months of program 3) |
A possible way to do this:
1) create dummy variables for all days
2) update dummy variables to 1 when dummy variable lies between startdate and enddate
3) sum values of dummy variable between startdate and enddate
However, I'm not sure if this is the most convenient way to do this.
I already managed to create dummy variables, but am unable to match variable names with values of columns. Furthermore, I have around 6000 rows, and the time span is more than 4000 days, so I want to prevent looping over both rows and columns for step 2/3.
Does anyone know a good solution for this?
With kind regards,
Nick
Comment