...
There has been a change in the Delft-FEWS code since April 2016, when the check on MC's is introduced.
Checking the amalgamate using SQL
While tuning the ImportAmalgamate the following sql can be used. Please outcomment the appropriate 7 day condition on creationTime to see what Tasks have been produced over the last week with external historical timeseries.
| 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 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--, MAX(ts.expiryTime) AS maxTimeSeriesExpirytime
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 |