Query to show tickets created on Monday's-PGSQL
Working on build's: 14500
PGSQL:
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", wo.description "Description", rrs.RESOLUTION AS "Resolution", longtodate(wo.CREATEDTIME) AS "Created 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 SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID WHERE wo.ISPARENT='1' AND (extract(dow FROM to_timestamp(wo.createdtime/1000)::TIMESTAMP) IN (1)) AND wo.createdtime>=<from_thisyear> and wo.createdtime<=<to_thisyear>
ORDER BY wo.createdtime DESC
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
New to ADSelfService Plus?
Related Articles
Query to show tickets created based on shift time ( PGSQL )
Tested in build PGSQL (14300) Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created ...Query to show tickets created and resolved during weekdays and weekends-PGSQL
Working on Builds: 14500 1. Mon-Sat 7 PM - 6 AM: a. Created tickets: SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", aau.FIRST_NAME AS "Requester", dpt.DEPTNAME AS "Department", cd.CATEGORYNAME AS "Category", scd.NAME AS ...Query to show tickets created during weekends (Saturdays and Sundays) (MSSQL)
Tested in build MSSQL (14306) SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...Query to show Created, Pending and Completed Requests ( PGSQL & MSSQL )
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created Last Week", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending from Last Week", count(case when wo.completedtime >= ...Query to show technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime) "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left ...