Query Report - To display Timespent along with Total, Consumed and Remaining hours
Applicable for Charge by Hour type.
PGSQL:
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", cast((sum(ct.TIMESPENT)/1000 * interval '1 second') as varchar) AS "Time Spent", aau.FIRST_NAME AS "Requester", std.STATUSNAME AS "Request Status", rrs.RESOLUTION AS "Resolution", sp.fixedmonthlyunits "Total Hours", (MAX(bc.consumedunits)/3600000) "Hours Used",MAX(sp.fixedmonthlyunits)-(MAX
(bc.consumedunits)/3600000) "Hours Remaining" 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 SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id inner join accountcontract ac on ac.accountid = asm.accountid left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid left join billcycle bc ON ac.contractid=bc.contractid WHERE ac.isactivecontract ='true' and std.internalname=
'Closed' and plantype='Charge Per Hour' group by 1,4,5,6,7 ORDER BY 1 NULLS FIRST