Query Report History by template name
Good Afternoon. I 've built a working query for reporting tickets that were previously in critical status. However, I only get results when I query for 'this month' and nothing for 'last month', ' last quester', 'last week' etc. Thank You in advance.
SELECT qd.QUEUENAME AS "Group", sdo.NAME AS "Site",pd.PRIORITYNAME AS "Priority", qd.QUEUENAME AS "Group", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester",wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician",longtodate(wo.CREATEDTIME) AS "Created Time", std.STATUSNAME AS "Request Status" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN workorderhistory wh ON wo.WORKORDERID=wh.WORKORDERID
LEFT JOIN workorderhistorydiff wd ON wh.HISTORYID=wd.HISTORYID
WHERE (wd.PREV_VALUE=('2'))
AND (wd.COLUMNNAME=('PRIORITYID'))
AND (std.STATUSID!=('3'))
AND (wo.CREATEDTIME>=(<from_thismonth>) AND wo.CREATEDTIME <= <to_thismonth>)
SELECT qd.QUEUENAME AS "Group", sdo.NAME AS "Site",pd.PRIORITYNAME AS "Priority", qd.QUEUENAME AS "Group", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester",wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician",longtodate(wo.CREATEDTIME) AS "Created Time", std.STATUSNAME AS "Request Status" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN workorderhistory wh ON wo.WORKORDERID=wh.WORKORDERID
LEFT JOIN workorderhistorydiff wd ON wh.HISTORYID=wd.HISTORYID
WHERE (wd.PREV_VALUE=('2'))
AND (wd.COLUMNNAME=('PRIORITYID'))
AND (std.STATUSID!=('3'))
AND (wo.CREATEDTIME>=(<from_thismonth>) AND wo.CREATEDTIME <= <to_thismonth>)