Query to show requests with contract billing, service plan and time spent details (MSSQL)
Working on : 14500
MSSQL:
SELECT "wo"."WORKORDERID" AS "Request ID", "aau"."FIRST_NAME" AS "Requester", "wo"."TITLE" AS "Subject", "qd"."QUEUENAME" AS "Group", "ti"."FIRST_NAME" AS "Technician", "std"."STATUSNAME" AS "Request Status", cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as varchar(20))+ 'Secs' "Time Spent", LONGTODATE("wo"."CREATEDTIME") AS "Created Time", "ac"."CONTRACTNAME" AS "Contract Name", sp.serviceplanname "Service Plan Name", "ad"."ORG_NAME" AS "Account" FROM "WorkOrder" "wo" 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 "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID" LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID" LEFT JOIN "WorkorderToContract" "wtc" ON "wo"."WORKORDERID"="wtc"."WORKORDERID" LEFT JOIN "AccountContract" "ac" ON "wtc"."CONTRACTID"="ac"."CONTRACTID" LEFT JOIN "AccountSiteMapping" "asm" ON "wo"."SITEID"="asm"."SITEID" LEFT JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid LEFT JOIN WorkOrderToCharge wotoc ON wotoc.WORKORDERID=wo.WORKORDERID LEFT JOIN chargestable ct ON ct.CHARGEID=wotoc.CHARGEID WHERE (wo.ISPARENT='1') AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> GROUP BY wo.WORKORDERID, aau.FIRST_NAME, wo.TITLE, qd.QUEUENAME, ti.FIRST_NAME, std.STATUSNAME, wo.CREATEDTIME, ac.CONTRACTNAME, sp.serviceplanname, ad.ORG_NAME
With Closed only tickets, with specific service plan and completed time period:
SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", rctd.FIRST_NAME AS "Time Spent Technician", ct.TS_STARTTIME AS "Time Spent Starttime", ct.DESCRIPTION AS "Time Spent Description", ct.TIMESPENT AS "Time Spent", ct.TOTAL_CHARGE AS "Time Spent Total_Charge" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID INNER JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID INNER JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID inner join accountcontract ac on ac.accountid = asm.accountid left join billhistory bh on ac.contractid=bh.contractid left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid left join billcycle bc ON ac.contractid=bc.contractid WHERE wo.ISPARENT='1' AND std.STATUSNAME = 'Closed' AND sp.serviceplanname='Time & Materials' AND wo.completedtime>=<from_lastmonth> and wo.completedtime<=<to_lastmonth> ORDER BY 1, 2
NOTE:
1. With Closed only tickets, with specific service plan and completed time period all highlighted which can be modified.
2. Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
New to ADSelfService Plus?
Related Articles
Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...Query to show both requests and task worklog time spent together ( MSSQL )
Tested in MSSQL build (14306) Timespent for both tasks and requests select wo.workorderid "Request ID", aau.FIRST_NAME "Requester", cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as ...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 time spent by each technician in a ticket (MSSQL & PGSQL )
Tested in Build PGSQL (14300) or MSSQL (14306) Navigate to Reports->New Query Report and execute this report. Under Help->About, check the database you are using and use the appropriate query. MSSQL: SELECT wo.WORKORDERID "Request ID",wo.TITLE ...Query to get Asset contract and its details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) SELECT mcdt.Contractid "URL Contract ID", mcdt.customcontractid "Custom contractid in details page", mcdt.CONTRACTNAME "Contract Name", aao.NAME "Maintenance Vendor Name", LONGTODATE(mcdt.FROMDATE) ...