Query to show requests with contract billing, service plan and time spent details

Query to show requests with contract billing, service plan and time spent details

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?
  1. 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
  2. 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>

          • Related Articles

          • Query to show contract billing details with requests

            PGSQL: ​ select ad.org_name "Account", wo.workorderid "Request ID", wo.workorderid "Request ID", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name", sp.timeperiod "Bill Cycle", cast((sum(ct.TIMESPENT)/1000 * interval '1 second') ...
          • Contract and Service Plans details - Query Report

            PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill Cycle",fixedmonthlycharges "Fixed Base Charge",sp.fixedmonthlyunits ...
          • Query to show both requests and task worklog time spent together

            MSSQL: 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 varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as ...
          • Query to show Time spent in each status - Time Analysis

            MSSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...
          • Query to show total time spent of a technician for the current month_PGSQL

            Query show total time spent by technician for the current month regardless of the ticket created date. PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS ...