Database/Conversion_runtime.sql at master · P3SW/Database · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
WITH TIMEDATA
AS (
	SELECT *
	FROM (
		SELECT *
		FROM (
			SELECT MANAGER
				,[KEY]
				,[VALUE]
				,RUN_NO
			FROM v_engine_properties
			) AS a
		WHERE [KEY] IN (
				'START_TIME'
				,'END_TIME'
				,'Læste rækker'
				,'Skrevne rækker'
				,'BUILD_JOB'
				,'BUILD_NUMBER'
				,'RUN_JOB'
				,'JENKINS_NODE'
				)
		) AS b
	pivot(MIN([VALUE]) FOR [KEY] IN (
				[START_TIME]
				,[END_TIME]
				,[Læste rækker]
				,[Skrevne rækker]
				,[BUILD_JOB]
				,[BUILD_NUMBER]
				,[RUN_JOB]
				,[JENKINS_NODE]
				)) AS c
	)
	,RUN_DATA
AS (
	SELECT MANAGER
		,SUBSTRING(MANAGER, CHARINDEX('.', MANAGER) + 1, LEN(MANAGER)) MANAGER_SHORT
		,START_TIME
		,END_TIME
		,ROWS_READ
		,ROWS_WRITTEN
		,BUILD
		,RUN_JOB
		,JENKINS_NODE
		,RUN_NO
	FROM (
		SELECT replace(replace(td.MANAGER, 'dk.aes.ans.konvertering.managers.', ''), 'com.netcompany.konvertering.', '') MANAGER
			,td.START_TIME START_TIME
			,td.END_TIME END_TIME
			,td.[Læste rækker] ROWS_READ
			,td.[Skrevne rækker] ROWS_WRITTEN
			,td.BUILD_JOB + '#' + td.BUILD_NUMBER AS BUILD
			,td.RUN_JOB RUN_JOB
			,td.JENKINS_NODE JENKINS_NODE
			,td.RUN_NO
		FROM TIMEDATA td
		) td
	)
	,TOTAL_RUNTIME
AS (
	SELECT MIN(START_TIME) START_TIME
		,MAX(END_TIME) END_TIME
	FROM RUN_DATA
	)
SELECT MANAGER_SHORT
	,START_TIME
	,END_TIME
	,FORMAT(CONVERT(DATETIME, ISNULL(CONVERT(DATETIME, END_TIME, 120), CURRENT_TIMESTAMP) - CONVERT(DATETIME, START_TIME, 120)), 'HH:mm:ss') DURATION_SHORT
	,ROWS_READ
	,ROWS_WRITTEN
FROM RUN_DATA
ORDER BY DURATION_SHORT DESC