Report for requests open longer than 2 weeks
Can someone offer guidance about the MYSQL query I could use for running a report to show me requests that have been open longer than two weeks. I have built a report using the custom builder but this obviously requires me to enter a date every time I run it for two weeks ago which is not ideal.
The code I have behind the custom report is:
SELECT wo.WORKORDERID "Request ID",sdo.NAME "Site",pd.PRIORITYNAME "Priority",lvd.LEVELNAME "Level", std.STATUSNAME "Request Status",wo.CREATEDTIME "Created Time" FROM WorkOrder wo LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE ((((wo.CREATEDTIME <= 1353023999000) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))) AND ((( std.STATUSNAME != 'Closed') AND ( std.STATUSNAME != 'Resolved')) OR ( std.STATUSNAME IS NULL))) AND (wo.SITEID IN (6003,604,2104,606,608,610,612,614,616,2402,618,620,2404,622,5103,3902,624,1502,626,628,4206,1801,634,905,636,638,640,642,1202,644,646,648,2702,4506,650,3602,652,3904,673,4803,5104,654,5102,5101,5105,3302,5703,656,2701,630,658,660,664,4806,4203,666,4503,668,670,672,6303,4815))) AND wo.ISPARENT=1
I want to configure this with a dynamic date so that it always looks for calls that hold a status not CLOSED or RESOLVED and have been open for two weeks or longer. When I have this I can then schedule it to email out to the required parties.
The code I have behind the custom report is:
SELECT wo.WORKORDERID "Request ID",sdo.NAME "Site",pd.PRIORITYNAME "Priority",lvd.LEVELNAME "Level", std.STATUSNAME "Request Status",wo.CREATEDTIME "Created Time" FROM WorkOrder wo LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE ((((wo.CREATEDTIME <= 1353023999000) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1))) AND ((( std.STATUSNAME != 'Closed') AND ( std.STATUSNAME != 'Resolved')) OR ( std.STATUSNAME IS NULL))) AND (wo.SITEID IN (6003,604,2104,606,608,610,612,614,616,2402,618,620,2404,622,5103,3902,624,1502,626,628,4206,1801,634,905,636,638,640,642,1202,644,646,648,2702,4506,650,3602,652,3904,673,4803,5104,654,5102,5101,5105,3302,5703,656,2701,630,658,660,664,4806,4203,666,4503,668,670,672,6303,4815))) AND wo.ISPARENT=1
I want to configure this with a dynamic date so that it always looks for calls that hold a status not CLOSED or RESOLVED and have been open for two weeks or longer. When I have this I can then schedule it to email out to the required parties.