SQLTeam.com | Weblogs | Forums

Query to perrform on database

#1

I would like to query all the tickets that are currently in OPEN state but show only those that were resolved or closed in the last 3 days. This query will get all tickets that were open and closed the last 3 days.

SELECT wo.WORKORDERID AS "Request ID", longtodate(wo.CREATEDTIME) AS "Created Time", mdd.MODENAME AS "Request Mode", std.statusname AS "Ticket Status", aau.FIRST_NAME AS "Requester", dpt.DEPTNAME AS "Department", cd.CATEGORYNAME AS "Category", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", longtodate(wo.RESOLVEDTIME) AS "Resolved Time" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID 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 STATUSDEFINITION std on std.statusid=wos.statusid left join AaaUser ti ON td.USERID=ti.USER_ID WHERE wo.createdtime>= ( <from_today> - 259200000 )

#2

Please state the dbms that this SQL is for: MySQL? Other?