Version : 11027

DB : PGSQL

OUTPUT :

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)