Query not working
This is for my query and I want the report results to all requests assigned to 'DEV TASKS' (this is an additional field) but the results are comming up mixed.
Also I have 3 additional date/time field, which table can I find these?
SELECT cd.CATEGORYNAME 'Category',scd.NAME 'Sub Category',wo.WORKORDERID 'Request ID',DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000) ,'%m-%d-%Y %k:%i') 'Created Time',DATE_FORMAT(FROM_UNIXTIME(wo.RESPONDEDTIME/1000) ,'%m-%d-%Y %k:%i') 'Responded Time',concat(TRUNCATE(((respondedtime-createdtime)/1000)/3600,0),'hrs ', TRUNCATE((((respondedtime-createdtime)/1000)/60)%60,0) ,'mts ', TRUNCATE(((respondedtime-createdtime)/1000)%60,0) , 'sec') 'Response Time',nt.NOTESTEXT 'Notes' FROM WorkOrder_Threaded wot, WorkOrder_Fields pick INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Notes nt ON wo.WORKORDERID=nt.WORKORDERID WHERE ((( std.STATUSNAME like 'Open') AND ((wo.CREATEDTIME >= <from_thisweek>) AND
((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)))) AND ((wo.CREATEDTIME <= <to_thisweek>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID and pick.UDF_CHAR3 like 'DEV TASKS' group BY 1,2
Also I have 3 additional date/time field, which table can I find these?
SELECT cd.CATEGORYNAME 'Category',scd.NAME 'Sub Category',wo.WORKORDERID 'Request ID',DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000) ,'%m-%d-%Y %k:%i') 'Created Time',DATE_FORMAT(FROM_UNIXTIME(wo.RESPONDEDTIME/1000) ,'%m-%d-%Y %k:%i') 'Responded Time',concat(TRUNCATE(((respondedtime-createdtime)/1000)/3600,0),'hrs ', TRUNCATE((((respondedtime-createdtime)/1000)/60)%60,0) ,'mts ', TRUNCATE(((respondedtime-createdtime)/1000)%60,0) , 'sec') 'Response Time',nt.NOTESTEXT 'Notes' FROM WorkOrder_Threaded wot, WorkOrder_Fields pick INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Notes nt ON wo.WORKORDERID=nt.WORKORDERID WHERE ((( std.STATUSNAME like 'Open') AND ((wo.CREATEDTIME >= <from_thisweek>) AND
((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)))) AND ((wo.CREATEDTIME <= <to_thisweek>) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID and pick.UDF_CHAR3 like 'DEV TASKS' group BY 1,2