timespent with account and non-operational hour in reports
I need a query for the timespent reports highlighting or adding the operational hours and non-operational hours .
Existing query:
SELECT wo.WORKORDERID AS "Request ID", ad.ORG_NAME AS "Account", rtdef.NAME AS "Request Type", wo.TITLE AS "Subject", ct.TIMESPENT AS "Time Spent", rctd.FIRST_NAME AS "Time Spent Technician", ct.TS_STARTTIME AS "Time Spent Starttime", ct.TS_ENDTIME AS "Time Spent Endtime", ct.DESCRIPTION AS "Time Spent Description" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id WHERE ((((ct.TS_STARTTIME >= 1527775200000) AND ((ct.TS_STARTTIME != 0) AND (ct.TS_STARTTIME IS NOT NULL))) AND ((ct.TS_STARTTIME <= 1530367199000) AND (((ct.TS_STARTTIME != 0) AND (ct.TS_STARTTIME IS NOT NULL)) AND (ct.TS_STARTTIME != -1)))) AND (wo.SITEID IN (8,301))) AND wo.ISPARENT='1' ORDER BY 7 NULLS FIRST
Is there a way to to change or add another column next to the 'Time Spent' column and have it 'Time Spent AH' for after-hours for non-operational hours spent , and then the existing renamed to 'Time Spent BH' to then only display operational hours timespent for technicians.
Your Version : 9.4 Build 9405
Database : postgres
Existing query:
SELECT wo.WORKORDERID AS "Request ID", ad.ORG_NAME AS "Account", rtdef.NAME AS "Request Type", wo.TITLE AS "Subject", ct.TIMESPENT AS "Time Spent", rctd.FIRST_NAME AS "Time Spent Technician", ct.TS_STARTTIME AS "Time Spent Starttime", ct.TS_ENDTIME AS "Time Spent Endtime", ct.DESCRIPTION AS "Time Spent Description" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id WHERE ((((ct.TS_STARTTIME >= 1527775200000) AND ((ct.TS_STARTTIME != 0) AND (ct.TS_STARTTIME IS NOT NULL))) AND ((ct.TS_STARTTIME <= 1530367199000) AND (((ct.TS_STARTTIME != 0) AND (ct.TS_STARTTIME IS NOT NULL)) AND (ct.TS_STARTTIME != -1)))) AND (wo.SITEID IN (8,301))) AND wo.ISPARENT='1' ORDER BY 7 NULLS FIRST
Is there a way to to change or add another column next to the 'Time Spent' column and have it 'Time Spent AH' for after-hours for non-operational hours spent , and then the existing renamed to 'Time Spent BH' to then only display operational hours timespent for technicians.
Your Version : 9.4 Build 9405
Database : postgres