Please help in writing the query to get the below result i have a Requested details table which contains the data in the below formate.
|10||124||Work In Progress||12/9/2018 11:16|
I want the result in the like below.
|ParentId||Total Resolved time (min)||Actual Resolved Time (min)|
total resolved time is the the total time take to resolve the ticket time difference resolved - Submitted . e.g for 123 request 12/9/2018 11:15-12/9/2018 10:30 = 45 mins
Actual resolved time is the Total resolve time - request on hold time.
for 123 request the total resolve time is 45 mins. , but the request is moved to on hold state 12/9/2018 10:55:00 AM and at 11:15 is moved directly to resolved state. so i have we have subtract 20 mins for the total resolve time . so the actual resolved time is 45-20=25 mins
For 124 request total resolved time is 75 min, the request is moved to on hold state at 12/9/2018 10:55:00 AM and then at 12/9/2018 11:16:00 AM the request is moved to work in progress state. we have subtract the 21 mins from the total resolve time. i.e. 75-21=54