Forum Discussion
Track missed message with timestamp
Hi Trebor84,
1.) how do we know which message has reader answered?
2.) could you provide expected result based on sample data?
You wouldn't know if a reader has answered a message as some messages don't require a response. Every reader should always go into the oldest message first which then timestamps the message with a read date/time. What I'm trying to accomplish is to find who isn't dealing with the oldest message in the queue first then for that message count how many times it was missed, who missed it, and what job the invividual dealt with instead.
I've updated my example to show how responses are recorded, but like I say some messages won't have a response but someone is still expected to "read" it to get the message out of the queue. I'm only interested in the messages "In" for this excercise. For info, outbound messages don't have a read date/time stamp as we don't track when our external suppliers read our responses.
| Date/Time Sent | Job ID | In/Out | Sender | Reader | Message | Read Date/Time Stamp |
| 01/01/2024 23:59 | J12345 | In | Company A | Alex G | Please can you help me with a query? | 02/01/2024 07:30 |
| 02/01/2024 00:05 | J12346 | In | Company B | Chris B | Sorry we are running late | 02/01/2024 07:25 |
| 02/01/2024 07:10 | J12347 | In | Company C | Jenny C | Please call me urgently | 02/01/2024 07:20 |
| 02/01/2024 07:30 | J12345 | Out | Alex G | Company A | How can I help? | |
| 02/01/2024 07:25 | J12346 | Out | Chris B | Company B | Thanks for the update | |
| 02/01/2024 07:20 | J12347 | Out | Jenny C | Company C | Ok will do |
Expected output:
| Date/Time Sent | Job ID | In/Out | Sender | Reader | Message | Read Date/Time Stamp | Missed By | Missed Opportunity |
| 01/01/2024 23:59 | J12345 | In | Company A | Alex G | Please can you help me with a query? | 02/01/2024 07:30 | Reader: Chris B (Job ID: J12346) Reader: Jenny C (Job ID: J12347) | 2 |
| 02/01/2024 00:05 | J12346 | In | Company B | Chris B | Sorry we are running late | 02/01/2024 07:25 | Reader: Jenny C (Job ID: J12347) | 1 |
| 02/01/2024 07:10 | J12347 | In | Company C | Jenny C | Please call me urgently | 02/01/2024 07:20 | 0 |