Changes Report query help
Hi
we use the below Query for our monthly Overtime reports.
SELECT ownaaa.first_name AS "Technician", chdt.changeid AS "Change ID", ownaaa1.first_name AS "Time Spent Technician", ownaaa.first_name AS "Technician", chdt.title AS "Title", (chargetable.timespent/60000) AS "Time Spent HH:MM", longtodate(chargetable.ts_starttime) AS "Time Spent Start Date", wltype.name AS "Billable/Overtime", chanf.udf_char2 AS "Change / Project", cstdd.name AS "Change Status", chargetable.total_charge AS "Total Charges" FROM chargestable chargetable LEFT JOIN changetocharge changecharge ON chargetable.chargeid = changecharge.chargeid LEFT JOIN tasktocharge ttc ON ttc.chargeid = chargetable.chargeid LEFT JOIN changetotaskdetails cttd ON cttd.taskid = ttc.taskid LEFT JOIN changedetails chdt ON chdt.changeid = cttd.changeid OR chdt.changeid = changecharge.changeid LEFT JOIN change_fields chanf ON chdt.changeid = chanf.changeid LEFT JOIN sduser ownsd ON chdt.technicianid = ownsd.userid LEFT JOIN aaauser ownaaa ON ownsd.userid = ownaaa.user_id LEFT JOIN aaauser ownaaa1 ON chargetable.technicianid = ownaaa1.user_id LEFT JOIN worklogtypedefinition wltype ON wltype.worklogtypeid = isnull(chargetable.worklogtypeid,3) LEFT JOIN change_stagedefinition cstdd ON cstdd.wfstageid = chdt.wfstageid
WHERE ((dateadd(s,(chargetable.ts_starttime / 1000),'1970/01/01') >= '2018/05/01') AND (dateadd(s,(chargetable.ts_starttime / 1000),'1970/01/01') <= '2018/05/31') AND (chdt.changeid != 0) ) AND wltype.name LIKE '%Overtime%' ORDER BY 3
Can someone perhaps assist by helping for the sum of the Timespent to be added below?
and if they can calculate it in Hours and not minutes.
Regards
Lucille
we use the below Query for our monthly Overtime reports.
SELECT ownaaa.first_name AS "Technician", chdt.changeid AS "Change ID", ownaaa1.first_name AS "Time Spent Technician", ownaaa.first_name AS "Technician", chdt.title AS "Title", (chargetable.timespent/60000) AS "Time Spent HH:MM", longtodate(chargetable.ts_starttime) AS "Time Spent Start Date", wltype.name AS "Billable/Overtime", chanf.udf_char2 AS "Change / Project", cstdd.name AS "Change Status", chargetable.total_charge AS "Total Charges" FROM chargestable chargetable LEFT JOIN changetocharge changecharge ON chargetable.chargeid = changecharge.chargeid LEFT JOIN tasktocharge ttc ON ttc.chargeid = chargetable.chargeid LEFT JOIN changetotaskdetails cttd ON cttd.taskid = ttc.taskid LEFT JOIN changedetails chdt ON chdt.changeid = cttd.changeid OR chdt.changeid = changecharge.changeid LEFT JOIN change_fields chanf ON chdt.changeid = chanf.changeid LEFT JOIN sduser ownsd ON chdt.technicianid = ownsd.userid LEFT JOIN aaauser ownaaa ON ownsd.userid = ownaaa.user_id LEFT JOIN aaauser ownaaa1 ON chargetable.technicianid = ownaaa1.user_id LEFT JOIN worklogtypedefinition wltype ON wltype.worklogtypeid = isnull(chargetable.worklogtypeid,3) LEFT JOIN change_stagedefinition cstdd ON cstdd.wfstageid = chdt.wfstageid
WHERE ((dateadd(s,(chargetable.ts_starttime / 1000),'1970/01/01') >= '2018/05/01') AND (dateadd(s,(chargetable.ts_starttime / 1000),'1970/01/01') <= '2018/05/31') AND (chdt.changeid != 0) ) AND wltype.name LIKE '%Overtime%' ORDER BY 3
Can someone perhaps assist by helping for the sum of the Timespent to be added below?
and if they can calculate it in Hours and not minutes.
Regards
Lucille