Query to find the change in Dueby time when the Site is changed (MSSQL)
Tested in build MSSQL (14306)
SELECT wo.WORKORDERID AS "Request ID", "rtdef"."NAME" AS "Request Type", "std"."STATUSNAME" AS "Request Status",
LONGTODATE(cast(cast(wohd1.prev_value as varchar) as bigint)) AS "Previous Due Date",
LONGTODATE(cast(cast(wohd1.current_value as varchar) as bigint)) AS "Current Due Date",longtodate("wo"."CREATEDTIME") AS "Created Time",
longtodate("wo"."COMPLETEDTIME") AS "Completed Time",
longtodate("wo"."RESOLVEDTIME") AS "Resolved Time",
sd1.name as "Site change from" ,
sd2.name as"Site Changed to" FROM WorkOrder wo
Left Join Workorderstates wos on wo.workorderid=wos.workorderid
LEFT JOIN "RequestTypeDefinition" "rtdef" ON "wos"."REQUESTTYPEID"="rtdef"."REQUESTTYPEID" LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID"
Left Join WorkorderHistory woh ON wo.WorkorderID=woh.WorkorderID
Left Join WorkorderHistoryDiff wohd ON woh.HistoryId=wohd.HistoryId
LEFT JOIN SDOrganization sd1 ON cast(cast(wohd.prev_value as varchar) as varchar)=sd1.org_id
LEFT JOIN SDOrganization sd2 ON cast(cast(wohd.current_value as varchar) as varchar)=sd2. org_id
Left Join WorkorderHistory woh1 ON wo.WorkorderID=woh1.WorkorderID
Left Join WorkorderHistoryDiff wohd1 ON woh1.HistoryId=wohd1.HistoryId
WHERE (wo.ISPARENT='1') and wohd1.ColumnName in ('DUEBYTIME') and wohd.ColumnName in ('SITEID') and wohd.historyid=wohd1.historyid
LONGTODATE(cast(cast(wohd1.prev_value as varchar) as bigint)) AS "Previous Due Date",
LONGTODATE(cast(cast(wohd1.current_value as varchar) as bigint)) AS "Current Due Date",longtodate("wo"."CREATEDTIME") AS "Created Time",
longtodate("wo"."COMPLETEDTIME") AS "Completed Time",
longtodate("wo"."RESOLVEDTIME") AS "Resolved Time",
sd1.name as "Site change from" ,
sd2.name as"Site Changed to" FROM WorkOrder wo
Left Join Workorderstates wos on wo.workorderid=wos.workorderid
LEFT JOIN "RequestTypeDefinition" "rtdef" ON "wos"."REQUESTTYPEID"="rtdef"."REQUESTTYPEID" LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID"
Left Join WorkorderHistory woh ON wo.WorkorderID=woh.WorkorderID
Left Join WorkorderHistoryDiff wohd ON woh.HistoryId=wohd.HistoryId
LEFT JOIN SDOrganization sd1 ON cast(cast(wohd.prev_value as varchar) as varchar)=sd1.org_id
LEFT JOIN SDOrganization sd2 ON cast(cast(wohd.current_value as varchar) as varchar)=sd2. org_id
Left Join WorkorderHistory woh1 ON wo.WorkorderID=woh1.WorkorderID
Left Join WorkorderHistoryDiff wohd1 ON woh1.HistoryId=wohd1.HistoryId
WHERE (wo.ISPARENT='1') and wohd1.ColumnName in ('DUEBYTIME') and wohd.ColumnName in ('SITEID') and wohd.historyid=wohd1.historyid
New to ADSelfService Plus?
Related Articles
Query to find status change, its time and comments (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need. SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request ...Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...Query to find Time elapsed by each status in requests( MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) MSSQL SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", scd.NAME ...Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...Query to show total time taken to resolve and total time onhold ( MSSQL )
Tested in build MSSQL (14306) SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...