-
Notifications
You must be signed in to change notification settings - Fork 0
/
sm-sysmodtime.txt
118 lines (102 loc) · 3.03 KB
/
sm-sysmodtime.txt
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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
UPDATE CM3RTESTM2
SET SYSMODTIME = GETDATE()
WHERE NUMBER IN
('CM0000010',
'CM0000011',
'CM0000012',
'CM0000013',
'CM0000014',
'CM0000015',
'CM0000016',
'CM0000020',
'CM0000021',
'CM0000022',
'CM0000023',
'CM0000024',
'CM0000025',
'CM0000026',
'CM0000027',
'CM0000028')
--------------------
/*
SELECT NUMBER,CURRENT_PHASE,CNF_RISK,RISK_ASSESSMENT
FROM CM3RTESTM1
WHERE RISK_ASSESSMENT IS NULL
ORDER BY NUMBER
SELECT m1.NUMBER,m2.INITIAL_IMPACT,m1.CNF_RISK,m1.RISK_ASSESSMENT
FROM CM3RTESTM1 m1
JOIN CM3RTESTM2 m2
ON m1.NUMBER = m2.NUMBER
WHERE m1.RISK_ASSESSMENT NOT IN ('1','2','3','4','5','6','7','8','9') OR
m1.RISK_ASSESSMENT IS NULL
ORDER BY m1.NUMBER
UPDATE CM3RTESTM1
SET RISK_ASSESSMENT = '9'
WHERE RISK_ASSESSMENT IS NULL AND CNF_RISK = '3'
UPDATE CM3RM2
SET SYSMODTIME = GETDATE()
WHERE NUMBER = 'CM0001911'
UPDATE CM3RTESTM1
SET RISK_ASSESSMENT = '9' ,
CNF_RISK = '3'
WHERE RISK_ASSESSMENT IS NULL AND CNF_RISK = '3'
UPDATE CM3RTESTM2
SET INITIAL_IMPACT = '3'
WHERE INITIAL_IMPACT IS NULL
SELECT m1.NUMBER,m2.INITIAL_IMPACT,m1.CNF_RISK,m1.RISK_ASSESSMENT
FROM CM3RTESTM1 m1
JOIN CM3RTESTM2 m2
ON m1.NUMBER = m2.NUMBER
WHERE m1.RISK_ASSESSMENT NOT IN ('1','2','3','4','5','6','7','8','9') OR
m1.RISK_ASSESSMENT = '9'
ORDER BY m1.NUMBER
SELECT m1.NUMBER,m1.CURRENT_PHASE,m2.INITIAL_IMPACT,m1.CNF_RISK,m1.RISK_ASSESSMENT
FROM CM3RTESTM1 m1
JOIN CM3RTESTM2 m2
ON m1.NUMBER = m2.NUMBER
WHERE (
m1.RISK_ASSESSMENT NOT IN ('1','2','3','4','5','6','7','8','9') OR
m1.RISK_ASSESSMENT = '9'
) AND
m1.CURRENT_PHASE <> 'CLOSED'
ORDER BY m1.NUMBER
SELECT NUMBER,CURRENT_PHASE
FROM CM3RTESTM1 m1
WHERE NUMBER NOT LIKE 'CM%'
ORDER BY NUMBER
SELECT m1.NUMBER,m1.CURRENT_PHASE,m2.INITIAL_IMPACT,m1.CNF_RISK,m1.RISK_ASSESSMENT
FROM CM3RTESTM1 m1
JOIN CM3RTESTM2 m2
ON m1.NUMBER = m2.NUMBER
WHERE (
m1.RISK_ASSESSMENT NOT IN ('1','2','3','4','5','6','7','8','9') OR
m1.RISK_ASSESSMENT = '9'
) AND
m1.CURRENT_PHASE <> 'CLOSED' AND m1.CURRENT_PHASE = 'NEW'
ORDER BY m1.NUMBER
UPDATE CM3RTESTM2
SET SYSMODTIME = GETDATE()
FROM CM3RTESTM1 m1
JOIN CM3RTESTM2 m2
ON m1.NUMBER = m2.NUMBER
WHERE m1.RISK_ASSESSMENT = '9' AND m1.CNF_RISK = '3' AND m1.CURRENT_PHASE = 'NEW'
*/
SELECT m1.NUMBER,m1.CURRENT_PHASE,m2.INITIAL_IMPACT,m1.CNF_RISK,m1.RISK_ASSESSMENT,m2.SYSMODTIME
FROM CM3RTESTM1 m1
JOIN CM3RTESTM2 m2
ON m1.NUMBER = m2.NUMBER
WHERE m1.CURRENT_PHASE <> 'CLOSED' AND
m1.CURRENT_PHASE = 'OPEN' AND
m2.SYSMODTIME >= '2010-01-05 14:18:20.193'
ORDER BY m2.SYSMODTIME
-----------------------------------
SELECT m1.NUMBER,m1.CURRENT_PHASE,m2.INITIAL_IMPACT,m1.CNF_RISK,m1.RISK_ASSESSMENT,m2.SYSMODTIME
FROM CM3RTESTM1 m1
JOIN CM3RTESTM2 m2
ON m1.NUMBER = m2.NUMBER
WHERE (
m1.RISK_ASSESSMENT NOT IN ('1','2','3','4','5','6','7','8','9') OR
m1.RISK_ASSESSMENT = '9'
) AND
m1.CURRENT_PHASE <> 'CLOSED' AND m1.CURRENT_PHASE = 'OPEN'
ORDER BY m2.SYSMODTIME