Tested in build PGSQL (14300) and MSSQL (14306)
SELECT rd.REGIONNAME "Region",
count(wo.workorderid) "Logged",
count(case when std.ispending='0' THEN 1 ELSE NULL END) "Resolved",
count(case when std.ispending='1' THEN 1 ELSE NULL END) "Backlog"
FROM WorkOrder wo 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 StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Sitedefinition sd ON wo.siteid=sd.siteid LEFT JOIN Regiondefinition rd ON sd.regionid=rd.regionid WHERE (wo.ISPARENT='1') and
wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth> group by rd.REGIONNAME
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>