CREATE OR REPLACE PROCEDURE RPT_SVR_USR_STAT_CLOGPEDTIME(I_STARTTIME IN VARCHAR2,
I_ENDTIME IN VARCHAR2,
I_UNITTREECODE IN VARCHAR2, --施工单位/岗/人
I_SPECIALTY IN VARCHAR2, --专业类型(多个时用';'号隔开)
I_SUBCLASS IN VARCHAR2, --用户等级
P_CURSOR OUT CIDA_DT.CURTYPE) AS
T_DBEGINDATE DATE;
T_DENDDATE DATE;
T_RANDOMCODE NUMBER;
V_LENGTH INT;
I_INDEX INT;
V_TREECODE VARCHAR2(512);
BEGIN
I_INDEX := INSTR(I_UNITTREECODE, ',');
IF I_INDEX > 0 THEN
V_LENGTH := I_INDEX - 1;
ELSE
V_LENGTH := LENGTH(I_UNITTREECODE);
END IF;
V_TREECODE := ',' || I_UNITTREECODE || ',';
T_DBEGINDATE := TO_DATE(I_STARTTIME || ' 00:00:00',
'YYYY-MM-DD HH24:MI:SS');
T_DENDDATE := TO_DATE(I_ENDTIME || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
RANDOM_NUM(T_RANDOMCODE);
IF I_INDEX < 1 THEN
--单选
INSERT INTO rpt_usr_tab_clogdate
(RANDNUM, AREATREECODE, PEDTIME, CLOGNUM, BETCLOGNUM )
SELECT T_RANDOMCODE,
SUBSTR(REPAIROPERCODE, 1, V_LENGTH + 4),
PEDTIME,
COUNT(1), -- 故障数
sum(CASE
WHEN istimeout = 0 THEN
1
ELSE
0
END) --及时修复数
FROM (SELECT B.*,
CASE
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 00:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'00 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 01:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 01:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'01 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 02:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 02:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'02 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 03:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 03:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'03 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 04:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 04:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'04 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 05:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 05:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'05 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 06:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 06:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'06 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 07:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 07:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'07 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 08:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 08:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'08 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 09:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 09:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'09 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 10:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 10:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'10 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 11:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 11:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'11 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 12:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 12:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'12 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 13:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 13:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'13 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 14:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 14:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'14 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 15:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 15:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'15 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 16:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 16:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'16 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 17:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 17:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'17 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 18:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 18:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'18 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
'19:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 19:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'19 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
'20:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 20:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'20 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 21:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 21:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'21 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 22:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 22:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'22 时段'
ELSE
'23 时段'
END PEDTIME
FROM RPT_SVR_USR_BASE B)
WHERE (I_SUBCLASS IS NULL OR I_SUBCLASS LIKE '%' || SUBCLASS || '%')
AND (I_SPECIALTY IS NULL OR
I_SPECIALTY LIKE '%' || SPECIALTYCODE || '%')
AND REPAIROPERCODE LIKE I_UNITTREECODE || '%'
AND NOT DISPATCHTIME IS NULL
AND FIRSTRECEPTTIME BETWEEN T_DBEGINDATE AND T_DENDDATE
GROUP BY SUBSTR(REPAIROPERCODE, 1, V_LENGTH + 4), PEDTIME;
COMMIT;
ELSE
INSERT INTO rpt_usr_tab_clogdate
(RANDNUM, AREATREECODE, PEDTIME, CLOGNUM, BETCLOGNUM )
SELECT T_RANDOMCODE,
SUBSTR(REPAIROPERCODE, 1, V_LENGTH + 4),
PEDTIME,
COUNT(1), -- 故障数
sum(CASE
WHEN istimeout = 0 THEN
1
ELSE
0
END) --及时修复数
FROM (SELECT B.*,
CASE
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 00:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'00 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 01:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 01:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'01 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 02:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 02:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'02 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 03:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 03:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'03 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 04:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 04:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'04 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 05:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 05:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'05 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 06:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 06:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'06 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 07:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 07:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'07 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 08:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 08:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'08 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 09:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 09:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'09 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 10:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 10:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'10 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 11:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 11:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'11 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 12:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 12:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'12 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 13:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 13:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'13 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 14:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 14:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'14 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 15:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 15:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'15 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 16:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 16:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'16 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 17:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 17:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'17 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 18:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 18:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'18 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
'19:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 19:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'19 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
'20:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 20:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'20 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 21:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 21:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'21 时段'
WHEN FIRSTRECEPTTIME BETWEEN
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 22:00:00',
'yyyy-mm-dd hh24:mi:ss') AND
TO_DATE(TO_CHAR(FIRSTRECEPTTIME, 'yyyy-mm-dd') ||
' 22:59:59',
'yyyy-mm-dd hh24:mi:ss') THEN
'22 时段'
ELSE
'23 时段'
END PEDTIME
FROM RPT_SVR_USR_BASE B)
WHERE (I_SUBCLASS IS NULL OR I_SUBCLASS LIKE '%' || SUBCLASS || '%')
AND (I_SPECIALTY IS NULL OR
I_SPECIALTY LIKE '%' || SPECIALTYCODE || '%')
AND V_TREECODE LIKE
'%,' || SUBSTR(REPAIROPERCODE, 0, V_LENGTH) || ',%'
AND NOT DISPATCHTIME IS NULL
AND archivetime BETWEEN T_DBEGINDATE AND T_DENDDATE
GROUP BY SUBSTR(REPAIROPERCODE, 1, V_LENGTH + 4), PEDTIME;
COMMIT;
END IF;
/* UPDATE rpt_usr_tab_clogdate A
SET AREANAME = (SELECT OBJECTNAME
FROM ORG_TREE OT
WHERE A.AREATREECODE = OT.TREECODE)
WHERE EXISTS (SELECT OBJECTNAME
FROM ORG_TREE OT
WHERE A.AREATREECODE = OT.TREECODE)
AND RANDNUM = T_RANDOMCODE;
COMMIT;*/
OPEN P_CURSOR FOR
SELECT -- AREANAME, --施工单位
PEDTIME,
SUM(CLOGNUM) CLOGNUM, --故障总数
SUM(BETCLOGNUM) BETCLOGNUM, --及时修复数
decode(nvl(SUM(CLOGNUM), 0), 0, 0, SUM(BETCLOGNUM) / SUM(CLOGNUM)) BETCLOGNUMRATE
FROM rpt_usr_tab_clogdate C
WHERE C.RANDNUM = T_RANDOMCODE
GROUP BY PEDTIME
order by PEDTIME;
DELETE rpt_usr_tab_clogdate WHERE RANDNUM = T_RANDOMCODE;
COMMIT;
END;
