Guest_imported
New member
- Jan 1, 1970
- 0
Hi,
I have a table with numerous columns which include a date column for the date of the event and a time column for the time of the event. What I need to do is identify (i.e. insert a value in another column if condition true) the rows that occur within 20 minutes of a specified row. The difficult bit is that the condition is only true if there are 5 values within the 20 minute timeframe. The example data below probably best describes my problem.
Row Date Time Cluster
1 12/12/00 01:20 n
2 12/12/00 01:41 y
3 12/12/00 01:44 y
4 12/12/00 01:45 y
5 12/12/00 01:50 y
6 12/12/00 01:53 y
7 12/12/00 01:55 y
8 12/12/00 02:20 n
n would be entered into the cluster field of row 1 as it is not in a cluster of at least 5 calls in 20 mins. this is also the case for row 8. Rows 2-7 would all have y inserted into the cluster column as they all occur in a cluster of at least 5 calls in 20 mins.
If anyone has some useful advice on how I could tackle/solve this problem it would be much appreciated
I have a table with numerous columns which include a date column for the date of the event and a time column for the time of the event. What I need to do is identify (i.e. insert a value in another column if condition true) the rows that occur within 20 minutes of a specified row. The difficult bit is that the condition is only true if there are 5 values within the 20 minute timeframe. The example data below probably best describes my problem.
Row Date Time Cluster
1 12/12/00 01:20 n
2 12/12/00 01:41 y
3 12/12/00 01:44 y
4 12/12/00 01:45 y
5 12/12/00 01:50 y
6 12/12/00 01:53 y
7 12/12/00 01:55 y
8 12/12/00 02:20 n
n would be entered into the cluster field of row 1 as it is not in a cluster of at least 5 calls in 20 mins. this is also the case for row 8. Rows 2-7 would all have y inserted into the cluster column as they all occur in a cluster of at least 5 calls in 20 mins.
If anyone has some useful advice on how I could tackle/solve this problem it would be much appreciated