Query to get Weekly count of request based on status
Version : 11027
DB : PGSQL
OUTPUT :
![](https://desk.zoho.com:443/support/ImageDisplay?downloadType=uploadedFile&fileName=1680860460141.png&blockId=6b6ce70e6c55f4e3d935db73e2fd45cbf57d9b02e22471ff&zgId=c065ea7ed2255947&mode=view)
Select case when
extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP) = '1' then 'Monday' when
extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP) = '2' then 'Tuesday' when
extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP) = '3' then 'Wednesday' when
extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP) = '4' then 'Thursday' when
extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP) = '5' then 'Friday' when
extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP) = '6' then 'Saturday' when
extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP) = '0' then 'Sunday' ELSE NULL END "DATE",
Count(wo.workorderid) "Total",
count(case when std.statusname='open' THEN 1 ELSE NULL END) "open status Requests",
count(case when std.statusname='In Progress' THEN 1 ELSE NULL END) "In Progress status Requests",
count(case when std.ispending='0' THEN 1 ELSE NULL END) "Closed Requests"from workorder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wo.ISPARENT='1') and wo.CREATEDTIME >= <from_lastweek> AND wo.CREATEDTIME <= <to_lastweek> group by extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP)
order by extract(dow from to_timestamp(wo.createdtime/1000)::TIMESTAMP)
New to ADSelfService Plus?