Complete worklog report -MSSQL

Complete worklog report -MSSQL

Query:

SELECT aau.FIRST_NAME AS "Requester",

max(wo.WORKORDERID) AS "Request ID",

rtdef.NAME AS "Request request type",

wo.TITLE AS "Subject",

wo.description ,

std.STATUSNAME AS "Request Status",

ti.FIRST_NAME AS "Assigned Technician",

Longtodate(wos.ASSIGNEDTIME) AS "Request assigned time",

Longtodate(wo.RESOLVEDTIME) AS "Resolved Time",

ct.SHORT_DESCRIPTION AS "Worklog Description",

Longtodate(ct.CREATEDTIME) As "Worklog added time",

CONVERT(VARCHAR(8), DATEADD(MILLISECOND, SUM(ct.TIMESPENT), 0), 108) "Work Log Time Spent",

ad.org_name AS "Account Name",

sdo.NAME AS "Site" FROM WorkOrder wo

LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID

LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID

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

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

LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID

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

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

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

LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID 

LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID

LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID 

LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID 

LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID 

WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_lastweek> AND wo.CREATEDTIME <= <to_thisweek>

GROUP BY aau.FIRST_NAME,rtdef.NAME,wo.TITLE,std.STATUSNAME,ti.FIRST_NAME,wos.ASSIGNEDTIME,wo.RESOLVEDTIME,wo.description,ad.org_name,sdo.NAME,ct.TIMESPENT,ct.SHORT_DESCRIPTION,ct.CREATEDTIME


                  New to ADSelfService Plus?

                    • Related Articles

                    • Query to retrieve worklog details

                      Tested in: 14610 (Postgres) Query: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", sdo.NAME AS "Site", ti.FIRST_NAME AS "Request Technician" ,au1.FIRST_NAME AS "Worklog Technician", TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' ...
                    • Query to show Last added worklog of a ticket ( MSSQL )

                      Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...
                    • Query report to get the successfully scanned assets (MSSQL & PGSQL)

                      Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...
                    • Query to show both task comments and worklog comments ( MSSQL )

                      Tested in MSSQL build (14306) SELECT "taskdet"."TASKID" AS "Task ID", "taskdet"."TASKID" AS "Task ID", "wotask"."WORKORDERID" AS "RequestID", cd.CATEGORYNAME AS "Request Category", "taskgroup"."QUEUENAME" AS "Group", "taskowner"."FIRST_NAME" AS ...
                    • Query to list the custom and query reports and the technician created (MSSQL & PGSQL)

                      Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...