Convert query to show minutes and Seconds
Hi. I have this query that works well in showing the time between 2 statuses. I am running into discrepancies, where the system is round the minutes. Is there a way to modify this query to show Hours, Minutes, and seconds? The query is in MSSQL.
Thank you very much.
Dave
SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",LONGTODATE(wo.completedTIME) completedTIME, woh.OPERATION "Operation",LONGTODATE(OPERATIONTIME) OPERATIONTIME,scd.NAME "Subcategory",icd.NAME "Item",ti.FIRST_NAME "Technician",qd.QUEUENAME "Group",
sd1.statusname "From status",
sd2.statusname "To status",
cast((wsi.timespent/60) as varchar(20)) +':'+cast((wsi.timespent % 60) as varchar(20)) "Time Spent (hrs)" ,(CASE(wo.IS_CATALOG_TEMPLATE) WHEN 1 THEN 'Service Request' ELSE 'Incident' END) "Type" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join wo_status_info wsi on woa.assessmentid=wsi.assessmentid
LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
left join statusdefinition sd1 on wsi.statusid=sd1.statusid
left join statusdefinition sd2 on wsi.nextstatusid=sd2.statusid WHERE (wo.ISPARENT='1') and wo.completedTIME >= <from_lastmonth> AND wo.completedTIME <= <to_lastmonth> AND sd1.statusname like 'Assigned' and sd2.statusname like 'Acknowledged' and woh.OPERATION like 'UPDATE' order BY wo.completedTIME DESC
Thank you very much.
Dave
SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",LONGTODATE(wo.completedTIME) completedTIME, woh.OPERATION "Operation",LONGTODATE(OPERATIONTIME) OPERATIONTIME,scd.NAME "Subcategory",icd.NAME "Item",ti.FIRST_NAME "Technician",qd.QUEUENAME "Group",
sd1.statusname "From status",
sd2.statusname "To status",
cast((wsi.timespent/60) as varchar(20)) +':'+cast((wsi.timespent % 60) as varchar(20)) "Time Spent (hrs)" ,(CASE(wo.IS_CATALOG_TEMPLATE) WHEN 1 THEN 'Service Request' ELSE 'Incident' END) "Type" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join wo_status_info wsi on woa.assessmentid=wsi.assessmentid
LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
left join statusdefinition sd1 on wsi.statusid=sd1.statusid
left join statusdefinition sd2 on wsi.nextstatusid=sd2.statusid WHERE (wo.ISPARENT='1') and wo.completedTIME >= <from_lastmonth> AND wo.completedTIME <= <to_lastmonth> AND sd1.statusname like 'Assigned' and sd2.statusname like 'Acknowledged' and woh.OPERATION like 'UPDATE' order BY wo.completedTIME DESC