...
While tuning the ImportAmalgamate the following sql can be used. Please enable the appropriate 7 day condition on creationTime for your database flavour to see what Tasks have been produced over the last week with external historical timeseries. Also the AdminInterface database trend information can be very useful for this purpose.
| Code Block | ||
|---|---|---|
| ||
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 |