Compare one timestamp against multiple other timestamps

[TenguMan]

Hello,

I am working with ServiceNow tickets and have been tasked with pulling assignment groups that were assigned to a ticket while it was in either "Open" or "Awaiting Info" states. I have the timestamps for when each state began and ended, I have the timestamps for when an assignment group was involved with a given ticket, but what complicates this is the fact that a ticket can be in one of those states multiple times.

Is there a way I can combine timestamps into a single measure that I would be able to use to compare against another timestamp? For instance, if a ticket was "Open" at 8:00 AM, went to "Awaiting Info" at 9:00 AM, and then back to "Open" at 10:00 AM for another hour, is there a way I can look for a time that falls into one of those two "Open" times?

[TenguMan]

To elaborate, I have two tables, one containing the timestamps for when a ticket's state began and ended and a second containing the timestamps for when an assignment group was assigned to a ticket.

Ticket	State	Begin	End
CC000001	Open	07/01/2024 8:00:00 AM	07/01/2024 8:50:00 AM
CC000001	Awaiting Info	07/01/2024 8:50:01 AM	07/01/2024 10:00:00 AM
CC000001	Open	07/01/2024 10:00:01 AM	07/01/2024 11:00:00 AM
CC000002	Awaiting Info	07/02/2024 9:00:00 AM	07/02/2024 12:00:00 PM

Ticket	Assignment Group	Start	End
CC000001	Team_1	07/01/2024 8:00:00AM	07/01/2024 11:00:00AM
CC000002	Team_1	07/02/2024 9:00:00AM	07/02/2024 10:00:00AM
CC000002	Team_2	07/02/2024 10:00:01 AM	07/02/2024 12:00:00 PM

The idea is to show, on one table, the assignment groups that were assigned to a given ticket during the timespans specified in the first table. I have tried to use a calculated column and the RELATED function to get the assignment group assigned to it while the ticket is in "Open" (using the ticket number as the related field), but it errors out due to there being multiple values where "State" is Open for a particular ticket.

[lbendlin · Accepted solution]

If you want to show this on one table then you will need to indicate the temporal granularity.  Is a 10 minute interval enough?

It will be much cleaner to show this in separate charts.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?