Priority wise requests count
Query
SELECT pd.PRIORITYNAME'Priority',SUM(CASE WHEN std.statusname LIKE 'open' THEN 1 ELSE 0 END)'open requests',
SUM(CASE WHEN wos.ownerid IS NULL THEN 1 ELSE 0 END)'Unassigned requests',
SUM(CASE WHEN std.ispending=1 AND std.STATUSSTOPCLOCK=1 THEN 1 ELSE 0 END)'In progress Requests',
COUNT(wo.workorderid)'Total Requests' FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition STD ON wos.STATUSID=std.STATUSID
LEFT JOIN prioritydefinition pd ON pd.PRIORITYID=wos.PRIORITYID
WHERE wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
GROUP BY pd.PRIORITYNAME
ORDER BY 1
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>
Krishna Bharat
ServiceDesk Plus - MSP Support team