Query to list the request and its worklogs with the hours spent and its charges (MSSQL)
Tested in build MSSQL (14306)
Use case
The query will list the request details with the timespent, hours consumed and its charge,
Sample Table
DB : Poatgres and MSSQL
Query
SELECT ad.ORG_NAME AS "Account",
ac.contractname "Contract Name",
wo.WORKORDERID AS "Request ID",
rctd.FIRST_NAME AS "Time Spent Technician",
longtodate(ct.TS_STARTTIME) AS "Time Spent Starttime",
longtodate(ct.TS_ENDTIME) AS "Time Spent Endtime",
ct.DESCRIPTION AS "Time Spent Description",
CASE WHEN nbrc.ISBILLABLEWORKLOG = 'true' THEN 'Yes' ELSE 'No' END AS "Is Billable",
CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time Spent",
CASE WHEN ct.TOTAL_CHARGE = '0' Then '0' ELSE (cast(ct.TOTAL_CHARGE AS FLOAT)/(CAST(ct.TIMESPENT AS FLOAT)/1000/60))*60 END AS "Hourly Rate",
ct.TOTAL_CHARGE AS "Time Spent Charge",
sp.serviceplanname As "Service Plan" FROM WorkOrder wo
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID
LEFT JOIN WorklogToContract wtc ON wtc.chargeid=ct.chargeid
LEFT JOIN AccountContract ac on wtc.CONTRACTID=ac.CONTRACTID
LEFT JOIN AccountDefinition ad ON ac.accountid=ad.ORG_ID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN WorkLogCharges wlc ON ct.CHARGEID=wlc.REQUESTCHARGEID
LEFT JOIN "NoBillRequestCharges" "nbrc" ON "ct"."CHARGEID"="nbrc"."REQUESTCHARGEID"
LEFT JOIN "ServicePlan" sp ON ac.serviceplanid=sp.serviceplanid
where ac.CONTRACTID IS NOT NULL and wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
group by ad.ORG_NAME,ac.contractname,wo.WORKORDERID,rctd.FIRST_NAME,ct.TS_STARTTIME,ct.TS_ENDTIME,ct.DESCRIPTION,nbrc.ISBILLABLEWORKLOG,ct.TIMESPENT,ct.TOTAL_CHARGE,sp.serviceplanname order by 3
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>
- This year - <from_thisyear> - <to_thisyear>
- Last year - <from_lastyear> - <to_lastyear>
New to ADSelfService Plus?
Related Articles
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 get total worklog time spent on a request. (PGSQL)
Tested in build PGSQL (14300) Scenario: Request1 worklog1-Total time taken to resolve=1 hr 20 mins. worklog2- Total time taken to resolve=1hr 10mins. While generating a report the total time taken to resolve for request1 should be 2 hrs 30mins. ...Query to find the Consumed Units (Charge by Requests / Hour) (PGSQL & MSSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports Tab >> New Query Report >> Run the below query Consumed units (Charge by Requests) : PGSQL & MSSQL: select ad.org_name "Account",sp.serviceplanname "Service Plan",longtodate(ac.startdate) ...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", ...Query to get the Request First Assigned time
Use case: First Assigned Time is the number of minutes, hours, or days between when a requester submits a request and when was a support representative assigned to the Request. It indicates how long it is in an unassigned state. Working on Build: ...