i want to add a new column to workorder table
this is the query i have for a progress report for a section of IT
SELECT std.STATUSNAME 'Request Status',wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester',wo.TITLE 'Subject',ti.FIRST_NAME 'Technician',std.STATUSNAME 'Request Status',wo.CREATEDTIME 'Created Time',wo.DUEBYTIME 'Dueby Time' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE ((((((ti.FIRST_NAME = 'Abdelkrim ELMaarouf') OR (ti.FIRST_NAME = 'Ashraf F.M. Ashour')) OR (ti.FIRST_NAME = 'Kefaya Mustafa')) OR (ti.FIRST_NAME = 'Mohammed Arshad')) OR (ti.FIRST_NAME = 'Sami Malik')) AND (std.ISPENDING = '1')) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 1
i need to further modify this as it should be the progress report for the last 30 days(sysdate - 30) and it should be in the order of first Open calls, then the rest. how can i do it??? it is possibel to add another field to state Open calls as 1, user testing calls as 2, and so on.. and then grou pby that new field asc.
how can i get it done?
SELECT std.STATUSNAME 'Request Status',wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester',wo.TITLE 'Subject',ti.FIRST_NAME 'Technician',std.STATUSNAME 'Request Status',wo.CREATEDTIME 'Created Time',wo.DUEBYTIME 'Dueby Time' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE ((((((ti.FIRST_NAME = 'Abdelkrim ELMaarouf') OR (ti.FIRST_NAME = 'Ashraf F.M. Ashour')) OR (ti.FIRST_NAME = 'Kefaya Mustafa')) OR (ti.FIRST_NAME = 'Mohammed Arshad')) OR (ti.FIRST_NAME = 'Sami Malik')) AND (std.ISPENDING = '1')) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 1
i need to further modify this as it should be the progress report for the last 30 days(sysdate - 30) and it should be in the order of first Open calls, then the rest. how can i do it??? it is possibel to add another field to state Open calls as 1, user testing calls as 2, and so on.. and then grou pby that new field asc.
how can i get it done?