Query to show tickets created on Monday's-PGSQL

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?
  1. 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
  2. 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 ADManager Plus?

                  New to ADSelfService Plus?