16.20 - Example SQL for Table Chart - Teradata Ecosystem Manager

Teradata® Ecosystem Manager User Guide

Product
Teradata Ecosystem Manager
Release Number
16.20
Published
December 2020
Language
English (United States)
Last Update
2020-12-23
dita:mapPath
crn1512743909724.ditamap
dita:ditavalPath
ft:empty
The following is example SQL to create a table chart that displays a report of job execution times.
SELECT ResourceId JobId, ResourceType JobType, TDPID, TRIM(sc.year_of_calendar)||':Wk'||TRIM(sc.week_of_year) Week, AVG(ProcDuration) AvgJobDuration
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 ((EndTS - StartTS) day to second) 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
	INNER JOIN sys_calendar.calendar sc
	ON sc.calendar_date = procd.StartDt
GROUP BY 1,2,3,4
The Report Viewer portlet displays the following table chart.