Common table expression
In PostgresSQL (PGADMIN) I can use ctes (common table expression or with) to create "virtual" tables for use in selects using more advanced filters, if we can say so.
I tried to use this on servicedesk plus and I couldn't. I would need to filter the column with the name "Difference in hours" but if I take the code and put it in the WHERE clause I can't, because I can't use aggregate functions in the where.
In PGADMIN would solve this using CTE / WITH.
I highlighted the column that I need to filter the values in the where clause.
SELECT wo.WORKORDERID "Chamado",
max(aau.FIRST_NAME) "Solicitante",
max(wo.TITLE) "Assunto",
max(ti.FIRST_NAME) "Tecnico",
max(qd.QUEUENAME) "Grupo",
max(cd.CATEGORYNAME) AS "Categoria",
longtodate(max(wo.CREATEDTIME)) "Data de Criação",
LONGTODATE(max(wo.RESPONDEDTIME)) "1 Resposta do chamado",
TO_CHAR((max(wo.respondedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') "Tempo de 1ª Resposta",
to_char(to_timestamp(max(c.createdtime)/ 1000.0), 'DD/MM/YYYY HH24:MI') "Ultima resposta solicitante",
to_char(to_timestamp(max(n.notificationdate) / 1000.0), 'DD/MM/YYYY HH24:MI') "Ultima Resposta Tecnico",
(case when max(c.createdtime) > max(n.notificationdate) then to_char(to_timestamp(max(c.createdtime)/ 1000.0), 'DD/MM/YYYY HH24:MI') else to_char(to_timestamp(max(n.notificationdate) / 1000.0), 'DD/MM/YYYY HH24:MI') end) "Ultima Interacao",
(case when max(c.createdtime) > max(n.notificationdate) then 'SOLICITANTE' else 'ANALISTA' end) "Origem Ultima Resposta",
(EXTRACT(EPOCH FROM current_timestamp - cast((case when max(c.createdtime) > max(n.notificationdate) then to_char(to_timestamp(max(c.createdtime)/ 1000.0), 'YYYY-MM-DD HH24:MI:SS') else to_char(to_timestamp(max(n.notificationdate) / 1000.0), 'YYYY-MM-DD HH24:MI:SS') end) as date))/3600)::Integer "Diferenca em horas" FROM WorkOrder wo
LEFT JOIN (select con.workorderid,con.createdtime from conversation con LEFT JOIN aaauser au1 on con.requesterid=au1.user_id LEFT JOIN conversationdescription cond ON con.CONVERSATIONID= cond.CONVERSATIONID ) c on c.workorderid=wo.workorderid
LEFT JOIN (select nw.workorderid,no.notificationdate from notify_workorder nw left join notification no on nw.notificationid=no.notificationid left join aaauser au2 on no.senderid=au2.user_id left join notificationtodesc nod on no.NOTIFICATIONID=nod.NOTIFICATIONID where no.senderid !=1) n on n.workorderid=wo.workorderid
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE wo.CREATEDTIME between datetolong('2019-01-01') and datetolong('2021-12-31')
AND qd.QUEUENAME in ('TII - Administrativo','TII - Nivel 3 Infra','TIE - C.F.T.V','TII - Nível 1','TII - Nível 2')
AND std.STATUSNAME NOT IN ('FECHADO','RESOLVIDO','CANCELADO')
AND cd.CATEGORYNAME NOT IN ('Manutenção Preventiva')
AND wo.CREATEDTIME != 0
AND (EXTRACT(EPOCH FROM current_timestamp - cast((case when max(c.createdtime) > max(n.notificationdate) then to_char(to_timestamp(max(c.createdtime)/ 1000.0), 'YYYY-MM-DD HH24:MI:SS') else to_char(to_timestamp(max(n.notificationdate) / 1000.0), 'YYYY-MM-DD HH24:MI:SS') end) as date))/3600) > 48
AND (case when c.createdtime > n.notificationdate then 'SOLICITANTE' else 'ANALISTA' end) = 'SOLICITANTE'
group by wo.WORKORDERID