Example SQL for Bar and Line Charts - Teradata Ecosystem Manager

Teradata Ecosystem Manager User Guide

Product
Teradata Ecosystem Manager
Release Number
16.00
Published
December 2016
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
qae1470238875438.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-3201
lifecycle
previous
Product Category
Analytical Ecosystem
The following is example SQL to create a line chart that displays job durations over a two-week period.
SELECT StartDt, SUM(ProcDuration) TotalJobDuration
FROM
	(
		SELECT ev.ResourceId, ev.ResourceType, ev.TDPId, ev.UOWId, 
			   MAX(CASE WHEN EventType='START' THEN EventTS ELSE NULL END) StartTS, MAX(CASE WHEN EventType='END' THEN EventTS ELSE NULL END) EndTS,
			   (CASE WHEN StartTS IS NOT NULL AND EndTS IS NOT NULL AND EndTS>StartTS THEN (TimestampDiffSeconds(StartTS, EndTS ) ) ELSE NULL END) ProcDuration,
			   CAST(StartTS AS DATE) StartDt
		FROM MSMEvent ev 
		INNER JOIN MSMResource r
		ON r.ResourceId=ev.ResourceId
		GROUP BY 1,2,3,4
	)procd
WHERE StartDt IS NOT NULL
GROUP BY 1
The Report Viewer portlet displays the following line chart.