Query to get Status change and time spent on each status per request

Query to get Status change and time spent on each status per request

Build: 8121
Database: MSSQL


OUTPUT :




select wo.workorderid "id", max(sd.statusname) "status",
case when sum(wsi.timespent) = '0' then cast(sum(wsi.timespent) as varchar) else concat(FLOOR(sum(wsi.timespent)/60),
 ' hours ', sum(wsi.timespent)%60, ' minutes') end as "time_spent"
 from workorder wo join wo_assessment wa on wo.workorderid=wa.workorderid
 join wo_status_info wsi on wa.assessmentid=wsi.assessmentid
 left join statusdefinition sd on wsi.statusid=sd.statusid
 where wsi.endtime is not null group by wo.workorderid, wsi.assessmentid

                  New to ADSelfService Plus?