Query to show PM task details with Next schedule and last ran time (MSSQL & PGSQL)

Query to show PM task details with Next schedule and last ran time (MSSQL & PGSQL)

Tested in Build PGSQL (14300) or MSSQL (14306)

Next schedule time :

SELECT swo.WORKORDERID "PM Task ID",

      adef.org_name "Account",

       sdo.name "Site",

       ti.SCHEDULE_TIME "Next Schedule Time",

       aau.FIRST_NAME "Requester",

       dpt.DEPTNAME "Department",

       cd.CATEGORYNAME "Category",

       scd.NAME "Subcategory",

       icd.NAME "Item",

       qd.QUEUENAME "Group",

       ati.FIRST_NAME "Technician",

       swo.TITLE "Subject",

       std.STATUSNAME "Request Status" FROM sworkorder swo

LEFT JOIN SDUser sdu ON swo.REQUESTERID=sdu.USERID

LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID

 left join sitedefinition sdef on sdef.siteid=swo.siteid 

left join accountsitemapping asm on asm.siteid=sdef.siteid 

left join accountdefinition adef on adef.org_id=asm.accountid 

left join sdorganization sdo on sdo.org_id=sdef.siteid

LEFT JOIN SWorkOrderStates swos ON swo.WORKORDERID=swos.WORKORDERID

LEFT JOIN QueueDefinition qd ON swos.QUEUEID=qd.QUEUEID

LEFT JOIN CategoryDefinition cd ON swos.CATEGORYID=cd.CATEGORYID

LEFT JOIN SubCategoryDefinition scd ON swos.SUBCATEGORYID=scd.SUBCATEGORYID

LEFT JOIN ItemDefinition icd ON swos.ITEMID=icd.ITEMID

LEFT JOIN SDUser td ON swos.OWNERID=td.USERID

LEFT JOIN AaaUser ati ON td.USERID=ati.USER_ID

LEFT JOIN StatusDefinition std ON swos.STATUSID=std.STATUSID

LEFT JOIN SREQTASK_INPUT si ON si.WORKORDERID=swo.WORKORDERID

LEFT JOIN task_input ti ON ti.INSTANCE_ID = si.INSTANCE_ID

LEFT JOIN schedule s ON ti.SCHEDULE_ID = s.SCHEDULE_ID

LEFT JOIN UserDepartment ud ON aau.USER_ID=ud.USERID

LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID 

ORDER BY 4

Last Ran time:

SELECT swo.WORKORDERID "PM Task ID",

      adef.org_name "Account",

       sdo.name "Site",

ti.execution_start_time "Last Ran Start time",

ti.execution_finish_time "Last Ran Finish time",

ti.SCHEDULE_TIME "Next Schedule Time",

       aau.FIRST_NAME "Requester",

       dpt.DEPTNAME "Department",

       cd.CATEGORYNAME "Category",

       scd.NAME "Subcategory",

       icd.NAME "Item",

       qd.QUEUENAME "Group",

       ati.FIRST_NAME "Technician",

       swo.TITLE "Subject",

       std.STATUSNAME "Request Status" FROM sworkorder swo

LEFT JOIN SDUser sdu ON swo.REQUESTERID=sdu.USERID

LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID

 left join sitedefinition sdef on sdef.siteid=swo.siteid 

left join accountsitemapping asm on asm.siteid=sdef.siteid 

left join accountdefinition adef on adef.org_id=asm.accountid 

left join sdorganization sdo on sdo.org_id=sdef.siteid

LEFT JOIN SWorkOrderStates swos ON swo.WORKORDERID=swos.WORKORDERID

LEFT JOIN QueueDefinition qd ON swos.QUEUEID=qd.QUEUEID

LEFT JOIN CategoryDefinition cd ON swos.CATEGORYID=cd.CATEGORYID

LEFT JOIN SubCategoryDefinition scd ON swos.SUBCATEGORYID=scd.SUBCATEGORYID

LEFT JOIN ItemDefinition icd ON swos.ITEMID=icd.ITEMID

LEFT JOIN SDUser td ON swos.OWNERID=td.USERID

LEFT JOIN AaaUser ati ON td.USERID=ati.USER_ID

LEFT JOIN StatusDefinition std ON swos.STATUSID=std.STATUSID

LEFT JOIN SREQTASK_INPUT si ON si.WORKORDERID=swo.WORKORDERID

LEFT JOIN task_input ti ON ti.INSTANCE_ID = si.INSTANCE_ID

LEFT JOIN schedule s ON ti.SCHEDULE_ID = s.SCHEDULE_ID

LEFT JOIN UserDepartment ud ON aau.USER_ID=ud.USERID

LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID 

ORDER BY 4