Summary:

This article helps you to retrieve information on SLA time taken for a particular Step/Phase and information on SLA clock stopped time and resumed time through SQL queries.

Discussion:

--The difference between the minimum (Changedate) of AuditTypeCode '90' and maximum (ChangedDate) of AuditTypeCode '100' gives you the time taken for a particular step in the below query.

select ap.ApplicationLName,mn.WFMinorItemID, mn.WFMinorItemDesc, adt.AuditTypeCode,min(adt.ChangedDate) from AMS_AuditTrail adt inner join AMS_ApplicationItem ai on ai.ApplicationItemID= adt.AuditTablePK
inner join AMS_WFMinorItemTpl mn on mn.WFMinorItemID = ai.ItemID
inner join AMS_Application ap on ap.ApplicationID= adt.ApplicationID
where AuditTypeCode in (90 )
and adt.ApplicationID='b75bda5b-a685-480c-b9c7-018054ed379e'
group by ap.ApplicationLName,mn.WFMinorItemID, mn.WFMinorItemDesc,adt.AuditTypeCode
order by 3 desc

select ap.ApplicationLName, mn.WFMinorItemDesc, adt.AuditTypeCode,max(adt.ChangedDate) from AMS_AuditTrail adt inner join AMS_ApplicationItem ai on ai.ApplicationItemID= adt.AuditTablePK
inner join AMS_WFMinorItemTpl mn on mn.WFMinorItemID = ai.ItemID
inner join AMS_Application ap on ap.ApplicationID= adt.ApplicationID
where AuditTypeCode in (100 )
and adt.ApplicationID='b75bda5b-a685-480c-b9c7-018054ed379e'
group by ap.ApplicationLName, mn.WFMinorItemDesc,adt.AuditTypeCode
order by 2 desc

--The difference between the minimum (Changedate) of AuditTypeCode '500 'and maximum (ChangedDate) of AuditTypeCode '501' gives you the time taken for a particular phase in the below query.

select ap.ApplicationLName,mj.WFMajorItemName, adt.AuditTypeCode,min(adt.ChangedDate) from AMS_AuditTrail adt inner join AMS_ApplicationItem ai on ai.ApplicationItemID= adt.AuditTablePK
inner join AMS_WFMajorItemTpl mj on mj.WFMajorItemID = ai.ItemID
inner join AMS_Application ap on ap.ApplicationID= adt.ApplicationID
where AuditTypeCode in (500 )
and adt.ApplicationID='b75bda5b-a685-480c-b9c7-018054ed379e'
group by ap.ApplicationLName,mj.WFMajorItemName,adt.AuditTypeCode

select ap.ApplicationLName, mj.WFMajorItemName, adt.AuditTypeCode,max(adt.ChangedDate) from AMS_AuditTrail adt inner join AMS_ApplicationItem ai on ai.ApplicationItemID= adt.AuditTablePK
inner join AMS_WFMajorItemTpl mj on mj.WFMajorItemID = ai.ItemID
inner join AMS_Application ap on ap.ApplicationID= adt.ApplicationID
where AuditTypeCode in (501)
and adt.ApplicationID='b75bda5b-a685-480c-b9c7-018054ed379e'
group by ap.ApplicationLName, mj.WFMajorItemName,adt.AuditTypeCode

--SQL query to retrieve information on SLA clock stopped time and resumed time

select ap.ApplicationLName,mj.WFMajorItemName, adt.AuditTypeCode, adt.ChangedDate from AMS_AuditTrail adt inner join AMS_ApplicationItem ai on ai.ApplicationItemID= adt.AuditTablePK
inner join AMS_WFMajorItemTpl mj on mj.WFMajorItemID = ai.ItemID
inner join AMS_Application ap on ap.ApplicationID= adt.ApplicationID
where AuditTypeCode in (401)
and adt.ApplicationID='4a569a1b-4b88-43f8-8bf0-9c914999d614'
group by ap.ApplicationLName,adt.AuditTypeCode,mj.WFMajorItemName,adt.ChangedDate

select ap.ApplicationLName, mj.WFMajorItemName, adt.AuditTypeCode,adt.ChangedDate from AMS_AuditTrail adt inner join AMS_ApplicationItem ai on ai.ApplicationItemID= adt.AuditTablePK
inner join AMS_WFMajorItemTpl mj on mj.WFMajorItemID = ai.ItemID
inner join AMS_Application ap on ap.ApplicationID= adt.ApplicationID
where AuditTypeCode in (400)
and adt.ApplicationID='4a569a1b-4b88-43f8-8bf0-9c914999d614'
group by ap.ApplicationLName, mj.WFMajorItemName,adt.AuditTypeCode,adt.ChangedDate