need help on this query report
hello,, could anyone help me on this report. i want to automate this report instead of a statis createddate between two constants, i want the report on sysdate - 30 days.
if you see the query there are static values to createdtime. i want to schedule this report to run every month, so the criteria should be sysdate-30 days, that means the last 30 days report. plss help!!!
SELECT std.STATUSNAME 'Request Status', wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester',wo.TITLE 'Subject',ti.FIRST_NAME 'Technician',
substring(CAST(DATEADD(second, wo.createdtime/1000, '01/01/1970') AS nvarchar(20)),1,11) AS 'Created Time',
substring(CAST(DATEADD(second, wo.duebytime/1000, '01/01/1970') AS nvarchar(20)),1,11) AS 'Dueby Time',
cast(substring(CAST(DATEADD(second, wo.completedtime/1000, '01/01/1970') AS nvarchar(20)),1,11) as nvarchar) AS 'Completed 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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 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 (((((dpt.DEPTNAME = 'Management Accounts') AND (cd.CATEGORYNAME = 'ARMS')) AND ((std.STATUSNAME = 'Closed') OR (std.STATUSNAME = 'Resolved'))) AND ((wo.CREATEDTIME >= 1209585600075) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)))) AND ((wo.CREATEDTIME <= 1212263999060) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID
union
SELECT std.STATUSNAME 'Request Status', wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester',wo.TITLE 'Subject',ti.FIRST_NAME 'Technician',
substring(CAST(DATEADD(second, wo.createdtime/1000, '01/01/1970') AS nvarchar(20)),1,11) AS 'Created Time',
substring(CAST(DATEADD(second, wo.duebytime/1000, '01/01/1970') AS nvarchar(20)),1,11) AS 'Dueby Time',
cast('Pending ' as nvarchar)
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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 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 (((dpt.DEPTNAME = 'Management Accounts') AND (cd.CATEGORYNAME = 'ARMS')) AND (std.ISPENDING = '1')) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 1
thanks!!!!!!!!!!
if you see the query there are static values to createdtime. i want to schedule this report to run every month, so the criteria should be sysdate-30 days, that means the last 30 days report. plss help!!!
SELECT std.STATUSNAME 'Request Status', wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester',wo.TITLE 'Subject',ti.FIRST_NAME 'Technician',
substring(CAST(DATEADD(second, wo.createdtime/1000, '01/01/1970') AS nvarchar(20)),1,11) AS 'Created Time',
substring(CAST(DATEADD(second, wo.duebytime/1000, '01/01/1970') AS nvarchar(20)),1,11) AS 'Dueby Time',
cast(substring(CAST(DATEADD(second, wo.completedtime/1000, '01/01/1970') AS nvarchar(20)),1,11) as nvarchar) AS 'Completed 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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 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 (((((dpt.DEPTNAME = 'Management Accounts') AND (cd.CATEGORYNAME = 'ARMS')) AND ((std.STATUSNAME = 'Closed') OR (std.STATUSNAME = 'Resolved'))) AND ((wo.CREATEDTIME >= 1209585600075) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)))) AND ((wo.CREATEDTIME <= 1212263999060) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID
union
SELECT std.STATUSNAME 'Request Status', wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester',wo.TITLE 'Subject',ti.FIRST_NAME 'Technician',
substring(CAST(DATEADD(second, wo.createdtime/1000, '01/01/1970') AS nvarchar(20)),1,11) AS 'Created Time',
substring(CAST(DATEADD(second, wo.duebytime/1000, '01/01/1970') AS nvarchar(20)),1,11) AS 'Dueby Time',
cast('Pending ' as nvarchar)
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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 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 (((dpt.DEPTNAME = 'Management Accounts') AND (cd.CATEGORYNAME = 'ARMS')) AND (std.ISPENDING = '1')) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 1
thanks!!!!!!!!!!