A query abaixo mostra as tarefas cujo prazos que estão em dia (Vencer) ou atrasados (Vencido). É possível adicionar mais condições, por exemplo, trazer apenas modelos de processos específicos. Ou adicionar novas informações para a consulta, por exemplo, o solicitante.
Essa consulta é muito utilizada no BI/BPA para indicadores.
SELECT CASE WHEN taskInstance.dueDate < GETDATE() THEN ‘Vencido’ ELSE ‘Vencer’ END AS StatusPrazo ,
YEAR(wfprocess .startDate) AS Ano,
MONTH(wfprocess .startDate) AS Mes,
taskInstance.code AS Codigo,
wfprocess.neoId AS NeoId,
processModel.title AS Processo,
taskInstance.taskName AS Tarefa,
taskInstance.dueDate AS Data,
neoUser.fullName AS Proprietario,
taskInstance.status AS StatusTarefa
FROM wfprocess , processModel , taskInstance , neoUser
WHERE wfprocess .model_neoId = processModel .neoId
AND taskInstance.processId = wfprocess.neoId
AND neoUser.neoId = taskInstance.owner_neoId
AND taskInstance.status = 2
AND wfprocess.finishDate IS NULL
Exemplo com informações do solicitante, e com filtros para processos específico:
SELECT CASE WHEN taskInstance.dueDate is null THEN ‘Concluído’
WHEN taskInstance.dueDate < GETDATE() THEN ‘Vencido’
ELSE ‘Vencer’ END AS StatusPrazo ,
YEAR(wfprocess.startDate) AS Ano,
MONTH(wfprocess.startDate) AS Mes,
wfprocess.code AS Codigo,
wfprocess.neoId AS NeoId,
processModel.title AS Processo,
taskInstance.taskName AS Tarefa,
taskInstance.dueDate AS Data,
neoUser.fullName AS Proprietario,
taskInstance.status AS StatusTarefa,
solicitante.fullName AS Solicitante
FROM wfprocess
LEFT JOIN taskInstance ON (taskInstance.processId = wfprocess.neoId)
LEFT JOIN neoUser ON (neoUser.neoId = taskInstance.owner_neoId)
JOIN processModel ON (wfprocess.model_neoId = processModel .neoId)
LEFT JOIN neoUser AS solicitante ON (solicitante.neoId = wfprocess.requester_neoId)
WHERE wfprocess.saved = 1
AND (taskInstance.status = 2 OR taskInstance.status IS NULL)
AND (processModel.title = ‘Fluxo – Requisição de Pessoal’ OR processModel.title = ‘Fluxo – Movimentação de Pessoal’)