Modify Report for Application Requests
I have this MSSQL query that allows me to report on selected resources in our Service Catalog. It works well, however I am having a hard time trying to include the ticket resolved status and limit the results to tickets created last year. I added std.statusname into the query, however when I do that, I can't figure out how to bound it. Any help you could provide would be greatly appreciated.
Dave B
SELECT WO_Resources.WOID "RequestID",wo.TITLE "Subject",aau.FIRST_NAME "Requester",ti.FIRST_NAME "Technician" ,LONGTODATE(wo.CREATEDTIME) "Created Time",CatalogResource.TITLE "Resource Question", Questions.QUESTION "Question", ResourcesQAMapping.ANSWER "Answer" from WO_Resources LEFT JOIN ResourcesQAMapping ON WO_Resources.UID=ResourcesQAMapping.MAPPINGID LEFT JOIN CatalogResource ON wo_resources.RESOURCEID=CatalogResource.UID LEFT JOIN Questions ON Questions.QUESTIONID=ResourcesQAMapping.QUESTIONID LEFT JOIN RequestTemplate_List ON CatalogResource.SERVICEID=RequestTemplate_List.TEMPLATEID LEFT JOIN Workorder wo ON WO_Resources.WOID=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 where ResourcesQAMapping.ANSWER = 'Account Profile' OR ResourcesQAMapping.ANSWER like 'Compass LG' OR ResourcesQAMapping.ANSWER like 'Order Entry' OR ResourcesQAMapping.ANSWER like 'Compass SG' OR ResourcesQAMapping.ANSWER like 'Compass'
Dave B
SELECT WO_Resources.WOID "RequestID",wo.TITLE "Subject",aau.FIRST_NAME "Requester",ti.FIRST_NAME "Technician" ,LONGTODATE(wo.CREATEDTIME) "Created Time",CatalogResource.TITLE "Resource Question", Questions.QUESTION "Question", ResourcesQAMapping.ANSWER "Answer" from WO_Resources LEFT JOIN ResourcesQAMapping ON WO_Resources.UID=ResourcesQAMapping.MAPPINGID LEFT JOIN CatalogResource ON wo_resources.RESOURCEID=CatalogResource.UID LEFT JOIN Questions ON Questions.QUESTIONID=ResourcesQAMapping.QUESTIONID LEFT JOIN RequestTemplate_List ON CatalogResource.SERVICEID=RequestTemplate_List.TEMPLATEID LEFT JOIN Workorder wo ON WO_Resources.WOID=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 where ResourcesQAMapping.ANSWER = 'Account Profile' OR ResourcesQAMapping.ANSWER like 'Compass LG' OR ResourcesQAMapping.ANSWER like 'Order Entry' OR ResourcesQAMapping.ANSWER like 'Compass SG' OR ResourcesQAMapping.ANSWER like 'Compass'