MySQL Query reports
Hi, we test demo and want to receive reports:
1. Time of reaction 1th line
a) Assigned time - Created time
SELECT YEAR(T.Date) "Year", MONTH(T.Date) "Month", MAX(T.Time) "Max", MIN(T.Time) "Min", AVG(T.Time) "Avg" FROM (SELECT FROM_UNIXTIME(wo.CREATEDTIME/1000) "Date", (wos.ASSIGNEDTIME - wo.CREATEDTIME)/1000 "Time" FROM WorkOrder as wo inner join WorkOrderStates wos on wo.workorderid = wos.workorderid T group by 1,2
But the report not correctly.
2. Time spent to resolved request (Resolved time (when request to be Resolve) - Assigned time)
now i have report only Completed time (when request to be closed) - Assigned time.
SELECT T.Name "Name", YEAR(T.Date) "Year", MONTH(T.Date) "Month", MAX(T.Time) "Max", MIN(T.Time) "Min", AVG(T.Time) "Avg" FROM (SELECT au.FIRST_NAME "Name", FROM_UNIXTIME(wo.COMPLETEDTIME/1000) "Date", (wo.COMPLETEDTIME - wos.ASSIGNEDTIME)/1000/60 "Time" FROM WorkOrder as wo inner join WorkOrderStates wos on wo.workorderid = wos.workorderid inner join AaaUser au on wos.OWNERID = au.USER_ID T group by 1,2,3
How i can count this?
Please help me.
1. Time of reaction 1th line
a) Assigned time - Created time
SELECT YEAR(T.Date) "Year", MONTH(T.Date) "Month", MAX(T.Time) "Max", MIN(T.Time) "Min", AVG(T.Time) "Avg" FROM (SELECT FROM_UNIXTIME(wo.CREATEDTIME/1000) "Date", (wos.ASSIGNEDTIME - wo.CREATEDTIME)/1000 "Time" FROM WorkOrder as wo inner join WorkOrderStates wos on wo.workorderid = wos.workorderid T group by 1,2
But the report not correctly.
b) Completed time - Resolved time, but Resolved time no in schema.
2. Time spent to resolved request (Resolved time (when request to be Resolve) - Assigned time)
now i have report only Completed time (when request to be closed) - Assigned time.
SELECT T.Name "Name", YEAR(T.Date) "Year", MONTH(T.Date) "Month", MAX(T.Time) "Max", MIN(T.Time) "Min", AVG(T.Time) "Avg" FROM (SELECT au.FIRST_NAME "Name", FROM_UNIXTIME(wo.COMPLETEDTIME/1000) "Date", (wo.COMPLETEDTIME - wos.ASSIGNEDTIME)/1000/60 "Time" FROM WorkOrder as wo inner join WorkOrderStates wos on wo.workorderid = wos.workorderid inner join AaaUser au on wos.OWNERID = au.USER_ID T group by 1,2,3
How i can count this?
Please help me.