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 >= <from_lastweek> AND wo.completedtime <= <to_lastweek> THEN 1 ELSE NULL END) "Closed Last Week"
FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE (wo.ISPARENT='1') and wo.CREATEDTIME >= <from_lastweek> AND wo.CREATEDTIME <= <to_lastweek>
order by 1
Total received, pending and closed:
SELECT
count(wo.workorderid) "Total Received",
count(case when std.ispending='1' THEN 1 ELSE NULL END) "Tickets Pending",
count(case when std.ispending='0' THEN 1 ELSE NULL END) "Tickets Closed"
FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= <from_thisyear>
AND wo.CREATEDTIME <= <to_thisyear>
order by 1