监控脚本其中日期验证

huangw425

最近几天在做新系统的监控，其中有个日期表要验证结果数据和当前日期的正确性，写一段比较死板的代码穷举类型来验证所有起止日期

穷举各类型，根据类型验证起止日期和当前日期在对应类型下的起止日期

返回验证结果

如果验证结果不为空且不为0 ，则对应类型的起止日期异常；

如果验证结果为空，则对应类型未在穷举验证内；

 

代码框的代码不能修改吗

1. select s.inc_type "日期类型",
   
2. 
   
3.        s.inc_start "开始日期",
   
4. 
   
5.        s.inc_end "结束日期",
   
6. 
   
7.        s.case "验证结果"
   
8. 
   
9.   from (select distinct eit.inc_type,
   
10. 
    
11.                         eit.inc_start,
    
12. 
    
13.                         eit.inc_end,
    
14. 
    
15.                         case
    
16. 
    
17.                           when eit.inc_type = 'D' then --日
    
18. 
    
19. 
    
20.                            eit.inc_start - to_char(sysdate - 1, 'yyyymmdd')
    
21. 
    
22.                           when eit.inc_type = 'DA1' then --日期往后增量
    
23. 
    
24. 
    
25.                            eit.inc_end - to_char(sysdate + 1, 'yyyymmdd')
    
26. 
    
27.                           when eit.inc_type = 'DA2' then
    
28. 
    
29.                            eit.inc_end - to_char(sysdate + 2, 'yyyymmdd')
    
30. 
    
31.                           when eit.inc_type = 'DD1' then--日期往前增量
    
32. 
    
33. 
    
34.                            eit.inc_start - to_char(sysdate - 2, 'yyyymmdd')
    
35. 
    
36.                           when eit.inc_type = 'DD2' then
    
37. 
    
38.                            eit.inc_start - to_char(sysdate - 3, 'yyyymmdd')
    
39. 
    
40.                           when eit.inc_type = 'M01' then --月根据每月第一天
    
41. 
    
42. 
    
43.                            eit.inc_end -
    
44. 
    
45.                            to_char(trunc(sysdate, 'mm'), 'yyyymmdd')
    
46. 
    
47.                           when eit.inc_type = 'M' then
    
48. 
    
49.                            eit.inc_end -
    
50. 
    
51.                            to_char(trunc(sysdate, 'mm'), 'yyyymmdd')
    
52. 
    
53.                           when eit.inc_type in ('W0', 'W7') then --周 对比当前日期和本周第几天来确定
    
54. 
    
55. 
    
56.                            eit.inc_end -
    
57. 
    
58.                            to_char(sysdate - to_char(sysdate, 'd') + 1,
    
59. 
    
60.                                    'yyyymmdd')
    
61. 
    
62.                           when eit.inc_type = 'W1' then
    
63. 
    
64.                            eit.inc_end -
    
65. 
    
66.                            to_char(sysdate - to_char(sysdate, 'd') + 2,
    
67. 
    
68.                                    'yyyymmdd')
    
69. 
    
70.                           when eit.inc_type = 'W2' then
    
71. 
    
72.                            eit.inc_end -
    
73. 
    
74.                            to_char(sysdate - to_char(sysdate, 'd') + 3,
    
75. 
    
76.                                    'yyyymmdd')
    
77. 
    
78.                           when eit.inc_type = 'W3' then
    
79. 
    
80.                            eit.inc_end -
    
81. 
    
82.                            to_char(sysdate - to_char(sysdate, 'd') + 4,
    
83. 
    
84.                                    'yyyymmdd')
    
85. 
    
86.                           when eit.inc_type = 'W4' then
    
87. 
    
88.                            eit.inc_end -
    
89. 
    
90.                            to_char(sysdate - to_char(sysdate, 'd') + 5,
    
91. 
    
92.                                    'yyyymmdd')
    
93. 
    
94.                           when eit.inc_type = 'W5' then
    
95. 
    
96.                            eit.inc_end -
    
97. 
    
98.                            to_char(sysdate - to_char(sysdate, 'd') + 6,
    
99. 
    
100.                                    'yyyymmdd')
     
101. 
     
102.                           when eit.inc_type = 'W6' then
     
103. 
     
104.                            eit.inc_end -
     
105. 
     
106.                            to_char(sysdate - to_char(sysdate, 'd') + 7,
     
107. 
     
108.                                    'yyyymmdd')
     
109. 
     
110.                           when eit.inc_type like 'Q%' then --季类型 分四季check月是否正确
     
111. 
     
112. 
     
113.                            SUBSTR(eit.inc_end, 5, 4) -
     
114. 
     
115.                            decode(to_char(sysdate, 'Q'),
     
116. 
     
117.                                   '1',
     
118. 
     
119.                                   '0101',
     
120. 
     
121.                                   '2',
     
122. 
     
123.                                   '0401',
     
124. 
     
125.                                   '3',
     
126. 
     
127.                                   '0701',
     
128. 
     
129.                                   '4',
     
130. 
     
131.                                   '1101')
     
132. 
     
133.                           when eit.inc_type = 'Y' then
     
134. 
     
135.                            SUBSTR(eit.inc_end, 1, 4) -
     
136. 
     
137.                            to_char(sysdate, 'YYYY')
     
138. 
     
139.                         end case
     
140. 
     
141.           from etl_inc_time_config eit
     
142. 
     
143.          where upper(eit.inc_type) <> 'INIT'
     
144. 
     
145.            and ((eit.inc_type like 'W%' and
     
146. 
     
147.                 substr(eit.inc_type, 2, 1) < to_char(sysdate, 'd')) or --周取数需到当前周期才生效
     
148. 
     
149. 
     
150.                 eit.inc_type not like 'W%')) s
     
151. 
     
152.  where s.case <> 0 --结果为0 则数据正常 非0 则与规则不符
     
153. 
     
154. 
     
155.     or s.case is null