Forum Discussion

mitchperkins's avatar
mitchperkins
Regular Visitor
3 years ago

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.

  1. Time from the ticket entering "Equipment Needed" to being moved to "Equipment Ordered" or "Ready to Schedule" (whichever comes first, both will happen on most tickets)
  2. Time from the ticket entering "Ready to Schedule" to being updated to any other status. 

 

Below is an example of the output provided for one ticket:

Need Help

2 Replies

  • mitchperkins's avatar
    mitchperkins
    Regular Visitor
    3 years ago

    Hello,

    Below is a sanitized sample:

    SR_Service_RecIDDate_Entered_UTCOld_ValueNew_ValueAudit_Text
    1733609/9/2022 16:17 NewStatus has been updated from "" to "New".
    1733609/9/2022 16:24NewEquipment NeededStatus has been updated from "New" to "Equipment Needed".
    1733689/9/2022 16:24 NewStatus has been updated from "" to "New".
    1733769/9/2022 16:31 NewStatus has been updated from "" to "New".
    1733829/9/2022 16:35 NewStatus has been updated from "" to "New".
    1733829/9/2022 16:36NewEquipment NeededStatus has been updated from "New" to "Equipment Needed".
    1733769/9/2022 16:36NewEquipment NeededStatus has been updated from "New" to "Equipment Needed".
    1733689/9/2022 16:36NewEquipment NeededStatus has been updated from "New" to "Equipment Needed".
    1733899/9/2022 16:40 NewStatus has been updated from "" to "New".
    1733969/9/2022 16:46 NewStatus has been updated from "" to "New".
    1733899/9/2022 16:50NewEquipment NeededStatus has been updated from "New" to "Equipment Needed".
    1733969/9/2022 16:50NewEquipment NeededStatus has been updated from "New" to "Equipment Needed".
    1733969/12/2022 17:45Equipment NeededReady to ScheduleStatus has been updated from "Equipment Needed" to "Ready to Schedule".
    1733829/12/2022 17:50Equipment NeededReady to ScheduleStatus has been updated from "Equipment Needed" to "Ready to Schedule".
    1733899/12/2022 18:09Equipment NeededReady to ScheduleStatus has been updated from "Equipment Needed" to "Ready to Schedule".
    1733829/12/2022 18:33Ready to Schedule>ClosedStatus has been updated from "Ready to Schedule" to ">Closed".
    1733609/12/2022 18:39Equipment NeededReady to ScheduleStatus has been updated from "Equipment Needed" to "Ready to Schedule".
    1733969/12/2022 18:53Ready to Schedule>ClosedStatus has been updated from "Ready to Schedule" to ">Closed".
    1733899/12/2022 18:58Ready to Schedule>ClosedStatus has been updated from "Ready to Schedule" to ">Closed".
    1733609/12/2022 19:05Ready to Schedule>ClosedStatus has been updated from "Ready to Schedule" to ">Closed".
    1733769/12/2022 19:08Equipment NeededReady to ScheduleStatus has been updated from "Equipment Needed" to "Ready to Schedule".
    1733769/12/2022 19:20Ready to Schedule>ClosedStatus has been updated from "Ready to Schedule" to ">Closed".
    1733769/12/2022 19:29>ClosedTechnician ScheduledStatus has been updated from ">Closed" to "Technician Scheduled".
    1733769/12/2022 20:20Technician Scheduled>ClosedStatus has been updated from "Technician Scheduled" to ">Closed".
    1733689/13/2022 16:10Equipment NeededEquipment OrderedStatus has been updated from "Equipment Needed" to "Equipment Ordered".
    1733689/14/2022 19:19Equipment OrderedReady to ScheduleStatus has been updated from "Equipment Ordered" to "Ready to Schedule".
    1733689/14/2022 19:24Ready to Schedule>ClosedStatus has been updated from "Ready to Schedule" to ">Closed".

     

    Below is what I would expect the outcome to be:

    SR_Service_RecIDEquipment NeededUpdated from ENTime to Address ENReady to ScheduleUpdated from RTSTime to Address RTS
    1733609/9/2022 16:249/12/2022 18:393.099/12/2022 18:399/12/2022 19:050.02
    1733689/9/2022 16:369/13/2022 16:103.989/14/2022 19:199/14/2022 19:240.00
    1733769/9/2022 16:369/12/2022 19:083.119/12/2022 19:089/12/2022 19:200.01
    1733829/9/2022 16:369/12/2022 17:503.059/12/2022 17:509/12/2022 18:330.03
    1733899/9/2022 16:509/12/2022 18:093.059/12/2022 18:099/12/2022 18:580.03
    1733969/9/2022 16:509/12/2022 17:453.049/12/2022 17:459/12/2022 18:530.05