Query to show Logged, Resolved and Backlog tickets based on a Region (MSSQL & PGSQL)

Query to show Logged, Resolved and Backlog tickets based on a Region (MSSQL & PGSQL)

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?

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>