Forum Discussion
Calculating time between status updated for each Rec_ID
I have a table v_rpt_TicketAudit that provides me with timestamps of each status update to a ticket. I would like to make two calculations for each ticket. Time from the ticket entering "Equipment ...
Hello,
Below is a sanitized sample:
| SR_Service_RecID | Date_Entered_UTC | Old_Value | New_Value | Audit_Text |
| 173360 | 9/9/2022 16:17 | New | Status has been updated from "" to "New". | |
| 173360 | 9/9/2022 16:24 | New | Equipment Needed | Status has been updated from "New" to "Equipment Needed". |
| 173368 | 9/9/2022 16:24 | New | Status has been updated from "" to "New". | |
| 173376 | 9/9/2022 16:31 | New | Status has been updated from "" to "New". | |
| 173382 | 9/9/2022 16:35 | New | Status has been updated from "" to "New". | |
| 173382 | 9/9/2022 16:36 | New | Equipment Needed | Status has been updated from "New" to "Equipment Needed". |
| 173376 | 9/9/2022 16:36 | New | Equipment Needed | Status has been updated from "New" to "Equipment Needed". |
| 173368 | 9/9/2022 16:36 | New | Equipment Needed | Status has been updated from "New" to "Equipment Needed". |
| 173389 | 9/9/2022 16:40 | New | Status has been updated from "" to "New". | |
| 173396 | 9/9/2022 16:46 | New | Status has been updated from "" to "New". | |
| 173389 | 9/9/2022 16:50 | New | Equipment Needed | Status has been updated from "New" to "Equipment Needed". |
| 173396 | 9/9/2022 16:50 | New | Equipment Needed | Status has been updated from "New" to "Equipment Needed". |
| 173396 | 9/12/2022 17:45 | Equipment Needed | Ready to Schedule | Status has been updated from "Equipment Needed" to "Ready to Schedule". |
| 173382 | 9/12/2022 17:50 | Equipment Needed | Ready to Schedule | Status has been updated from "Equipment Needed" to "Ready to Schedule". |
| 173389 | 9/12/2022 18:09 | Equipment Needed | Ready to Schedule | Status has been updated from "Equipment Needed" to "Ready to Schedule". |
| 173382 | 9/12/2022 18:33 | Ready to Schedule | >Closed | Status has been updated from "Ready to Schedule" to ">Closed". |
| 173360 | 9/12/2022 18:39 | Equipment Needed | Ready to Schedule | Status has been updated from "Equipment Needed" to "Ready to Schedule". |
| 173396 | 9/12/2022 18:53 | Ready to Schedule | >Closed | Status has been updated from "Ready to Schedule" to ">Closed". |
| 173389 | 9/12/2022 18:58 | Ready to Schedule | >Closed | Status has been updated from "Ready to Schedule" to ">Closed". |
| 173360 | 9/12/2022 19:05 | Ready to Schedule | >Closed | Status has been updated from "Ready to Schedule" to ">Closed". |
| 173376 | 9/12/2022 19:08 | Equipment Needed | Ready to Schedule | Status has been updated from "Equipment Needed" to "Ready to Schedule". |
| 173376 | 9/12/2022 19:20 | Ready to Schedule | >Closed | Status has been updated from "Ready to Schedule" to ">Closed". |
| 173376 | 9/12/2022 19:29 | >Closed | Technician Scheduled | Status has been updated from ">Closed" to "Technician Scheduled". |
| 173376 | 9/12/2022 20:20 | Technician Scheduled | >Closed | Status has been updated from "Technician Scheduled" to ">Closed". |
| 173368 | 9/13/2022 16:10 | Equipment Needed | Equipment Ordered | Status has been updated from "Equipment Needed" to "Equipment Ordered". |
| 173368 | 9/14/2022 19:19 | Equipment Ordered | Ready to Schedule | Status has been updated from "Equipment Ordered" to "Ready to Schedule". |
| 173368 | 9/14/2022 19:24 | Ready to Schedule | >Closed | Status has been updated from "Ready to Schedule" to ">Closed". |
Below is what I would expect the outcome to be:
| SR_Service_RecID | Equipment Needed | Updated from EN | Time to Address EN | Ready to Schedule | Updated from RTS | Time to Address RTS |
| 173360 | 9/9/2022 16:24 | 9/12/2022 18:39 | 3.09 | 9/12/2022 18:39 | 9/12/2022 19:05 | 0.02 |
| 173368 | 9/9/2022 16:36 | 9/13/2022 16:10 | 3.98 | 9/14/2022 19:19 | 9/14/2022 19:24 | 0.00 |
| 173376 | 9/9/2022 16:36 | 9/12/2022 19:08 | 3.11 | 9/12/2022 19:08 | 9/12/2022 19:20 | 0.01 |
| 173382 | 9/9/2022 16:36 | 9/12/2022 17:50 | 3.05 | 9/12/2022 17:50 | 9/12/2022 18:33 | 0.03 |
| 173389 | 9/9/2022 16:50 | 9/12/2022 18:09 | 3.05 | 9/12/2022 18:09 | 9/12/2022 18:58 | 0.03 |
| 173396 | 9/9/2022 16:50 | 9/12/2022 17:45 | 3.04 | 9/12/2022 17:45 | 9/12/2022 18:53 | 0.05 |