SELECT t1.workflowId, t1.maxTaskExpiry, t1.taskCnt, t1.taskRunCnt, t1.maxTaskRunExpiryTime, t2.timeSeriesCnt, t2.maxTimeSeriesExpirytime FROM (
SELECT t3.workflowId, t3.maxTaskExpiry, t3.taskCnt, t4.taskRunCnt, t4.maxTaskRunExpiryTime FROM (
SELECT workflowId, MAX(expiryTime) AS maxTaskExpiry, count(*) AS taskCnt FROM Tasks
--WHERE creationTime > TRUNC(sysdate) - INTERVAL '7' DAY --oracle
--WHERE creationTime > current_date - interval '7 days' --postgresql
--WHERE creationTime > DATEADD(DAY,-7, GETDATE()) -- sqlserver
WHERE workflowId IN (SELECT workflowId FROM Tasks WHERE taskId IN (SELECT taskId FROM TaskRuns WHERE taskRunId IN (SELECT creatorTaskRunId FROM TimeSeries WHERE timeSeriesType IN (2,3))))
GROUP BY workflowId
) t3 LEFT JOIN (
SELECT workflowId, count(tr.taskRunId) AS taskRunCnt, max(tr.expiryTime) AS maxTaskRunExpiryTime
FROM Tasks t, TaskRuns tr
WHERE t.taskId=tr.taskId
GROUP BY workflowId
) t4 ON t3.workflowId=t4.workflowId
) t1 LEFT JOIN (
SELECT workflowId, count(1) AS timeSeriesCnt, MAX(ts.expiryTime) AS maxTimeSeriesExpirytime
FROM Tasks t, TaskRuns tr, TimeSeries ts
WHERE t.taskId=tr.taskId AND ts.creatorTaskRunId=tr.taskRunId
GROUP BY workflowId
) t2 ON t1.workflowId=t2.workflowId
ORDER BY t1.workflowId |