Forum Discussion
Helper IComparing Date/Times to other rows in the same table
Hello. I'm looking for some guidance on the best way of comparing datetimes to other rows in the same table. Here's some sample data of what I have:
The data in the table represents blocks of time for events
| Event Id | Block Start Time | Block End Time | Overlapping? |
| gk56jg3j5g3g63kg | 02/01/2000 15:10:00 | 02/01/2000 15:18:00 | No |
| g345vnmhj5g23k4 | 02/01/2000 09:01:00 | 02/01/2000 11:48:00 | No |
| jj6jkl6j54kj6kl4j6kj | 01/01/2000 15:17:00 | 01/01/2000 16:00:00 | Yes |
| 3456uhyiy5364gg | 01/01/2000 14:16:00 | 01/01/2000 15:18:00 | Yes |
I want to calcuate the 4th column, for the values in red - looking to mark any blocks which overlap with any others
Essentially, if the current row's start time is after another's end time - or - it's start time is before another's end time (provided that block started before the current one)
Any help/guidance/suggestions appreicated
jasmith866 , Create a new column
New col =
var _cnt = countx(filter(Table, [Event Id] =earlier([Event Id]) && ( ( [Block Start Time] <= earlier([Block Start Time]) && [Block End Time] >= earlier([Block Start Time]) ) || ( [Block Start Time] <= earlier([Block End Time]) && [Block End Time] >= earlier([Block End Time]) ))), [Event Id])
return
if(isblank(_cnt), "No", "Yes")
2 Replies
- amitchandak
Super User
jasmith866 , Create a new column
New col =
var _cnt = countx(filter(Table, [Event Id] =earlier([Event Id]) && ( ( [Block Start Time] <= earlier([Block Start Time]) && [Block End Time] >= earlier([Block Start Time]) ) || ( [Block Start Time] <= earlier([Block End Time]) && [Block End Time] >= earlier([Block End Time]) ))), [Event Id])
return
if(isblank(_cnt), "No", "Yes")
jasmith866Thank you - EARLIER() was the context I was missing!