So I came across another problem. When looking at the data, I found that we respond to some of the messages by phone rather than using our messaging system. So, I added another option to catch the response time by looking at documentation itself, specifically the description of the document.
I have two descriptions I need to look at; Ask A Staff & Append. An "Ask A Staff" document is the original message sent where, "Append" is the response to that message.
Both of these documents are tagged with a time called "CONTB_TIME".
At first I thought I could change use the following expression:
This did compare the received date to the document date however, I think I need to clean this up a bit. My next thought would be that I could compare date/time between the two document types based on closet date range for the response time frame then I should get better results.
How can I rewrite the above expression to compare document date ranges against the two document types?
Hi Nathan - not sure i completely follow. Is this new requirement for a completely separate data set as the first thread ? Or did the data in the first thread already have both of these 2 descriptions in it already and there is some business logic we did not account for ?
No, this was data added after the fact based on what I found in our records. I realized yesterday evening that trying to compare the date/time between the two documents is a dead end. Our system gives the same date of the original document (Ask A Staff) as the new document added at a later time (Append). So their is no lapse time.
After applying the expression you gave me I found that we were getting messages in but not always replying back using a message. We in some cases replied using a phone call which meant I need to add document data to figure out if we replied by message or document to show we DID response in one way or another and how long it took for us to response.
The tables even though they are completely separate are still tied by the ProfileID. Where is gets tricky is to figure out the response time is I need to figure out which Appended document goes with an Ask A Staff question then apply that date/time against the received date/time to figure out a lapse time frame. In doing this I still need to grab the lapse time between the original comparison you help me with the other day.
Received_Date - Sent_Date if null than Append_Date if Date is same Ask A Staff_Date = lapse time (hh:mm)
Your original expression was great, it showed me that I already had an issue that I needed to address with our staff in the way they are using our messaging system!!