CREATE OR REPLACE PROCEDURE UP_GET_CENTER_UNCLOSED_1( P_NEWSHIFT_ID IN INT, P_NEWSHIFTDATE IN DATE
)AS
BEGIN
SELECT MASTER.ORGAN_ID,MASTER.ORGAN_NAME,B.SHIFT_NAME AS SHIFTNAME,TOTAL_COUNT,SENDED_COUNT,RETURN_FOR_EXCEPT,NO_RETURN_COUNT,RETURN_COUNT,DATA_NORMAL,DATA_BAD,DATA_PAY,DATA_NOPAY,CASE COALESCE(SUB.ROAD_NO,-100) WHEN -100 THEN 0 ELSE 1END AS BRANCH_CLOSED,CASE COALESCE(SUB.CENTER_CLOSED,0) WHEN 0 THEN 0 WHEN 1 THEN 1END AS CENTER_CLOSED FROM ECDBA.BASE_ORGAN MASTERLEFT JOIN SPM_FOR_CENTER_SHIFTS_TMP SUBON MASTER.ORGAN_LEVEL= SUB.ROAD_NOAND NEWSHIFT_ID = P_NEWSHIFT_IDAND NEWSHIFT_DATE = P_NEWSHIFTDATELEFT JOIN ECDBA.BASE_SHIFTS BON SUB.NEWSHIFT_ID = B.SHIFT_IDWHERE MASTER.ORGAN_LEVEL = 10ORDER BY BRANCH_CLOSED DESC,CENTER_CLOSED ASC;--删除上次遗留的数据DELETE FROM SPM_FOR_CENTER_SHIFTS_TMP WHERE NEWSHIFT_ID = P_NEWSHIFT_ID AND NEWSHIFT_DATE = P_NEWSHIFTDATE;
--向临时表插入数据INSERT INTO SPM_FOR_CENTER_SHIFTS_TMP(ROAD_NO,NEWSHIFT_ID,NEWSHIFT_DATE,TOTAL_COUNT,SENDED_COUNT,RETURN_FOR_EXCEPT,NO_RETURN_COUNT,RETURN_COUNT,DATA_NORMAL,DATA_BAD,DATA_PAY,DATA_NOPAY,CENTER_CLOSED)
SELECTORGAN_ID,SHIFT_ID,SHIFT_DATE,TOTAL_COUNT,(TOTAL_COUNT - UN_SEND_COUNT) AS SENDED_COUNT,(TOTAL_COUNT - UN_SEND_COUNT) AS RETURN_FOR_EXCEPT,SEND_COUNT AS NO_RETURN_COUNT,(DATA_NORMAL+DATA_BAD+DATA_PAY+DATA_NOPAY) AS RETURN_COUNT,DATA_NORMAL,DATA_BAD,DATA_PAY,DATA_NOPAY,CENTER_CLOSEDFROM(
SELECTORGAN_ID,MAX(NEWSHIFT_ID) AS SHIFT_ID,MAX(NEWSHIFT_DATE) AS SHIFT_DATE,SUM(TOTAL_COUNT) AS TOTAL_COUNT,SUM(UN_SEND_COUNT) AS UN_SEND_COUNT,SUM(SEND_COUNT) AS SEND_COUNT,SUM(DATA_NORMAL) AS DATA_NORMAL,SUM(DATA_BAD) AS DATA_BAD,SUM(DATA_PAY) AS DATA_PAY,SUM(DATA_NOPAY) AS DATA_NOPAY,CENTER_CLOSEDFROM(SELECT DISTINCT(OPERATOR_ID),ORGAN_ID,NEWSHIFT_ID,NEWSHIFT_DATE,( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS TOTAL_COUNT,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = -2 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS UN_SEND_COUNT,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = -1 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS SEND_COUNT,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = 0 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS DATA_NORMAL,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = 1 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS DATA_BAD,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = 2 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS DATA_PAY, ( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = 3 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE
) AS DATA_NOPAY, A.CENTER_CLOSEDFROM SPM_FOR_BRANCH_SHIFTS AINNER JOIN ECDBA.BASE_ORGAN CON C.ORGAN_ID = A.ROAD_NOWHERE A.NEWSHIFT_ID = P_NEWSHIFT_IDAND A.NEWSHIFT_DATE = P_NEWSHIFTDATEAND C.ORGAN_LEVEL = 10) TMPGROUP BY ORGAN_ID,CENTER_CLOSED) T1;END;存储Oracleselect
)AS
BEGIN
SELECT MASTER.ORGAN_ID,MASTER.ORGAN_NAME,B.SHIFT_NAME AS SHIFTNAME,TOTAL_COUNT,SENDED_COUNT,RETURN_FOR_EXCEPT,NO_RETURN_COUNT,RETURN_COUNT,DATA_NORMAL,DATA_BAD,DATA_PAY,DATA_NOPAY,CASE COALESCE(SUB.ROAD_NO,-100) WHEN -100 THEN 0 ELSE 1END AS BRANCH_CLOSED,CASE COALESCE(SUB.CENTER_CLOSED,0) WHEN 0 THEN 0 WHEN 1 THEN 1END AS CENTER_CLOSED FROM ECDBA.BASE_ORGAN MASTERLEFT JOIN SPM_FOR_CENTER_SHIFTS_TMP SUBON MASTER.ORGAN_LEVEL= SUB.ROAD_NOAND NEWSHIFT_ID = P_NEWSHIFT_IDAND NEWSHIFT_DATE = P_NEWSHIFTDATELEFT JOIN ECDBA.BASE_SHIFTS BON SUB.NEWSHIFT_ID = B.SHIFT_IDWHERE MASTER.ORGAN_LEVEL = 10ORDER BY BRANCH_CLOSED DESC,CENTER_CLOSED ASC;--删除上次遗留的数据DELETE FROM SPM_FOR_CENTER_SHIFTS_TMP WHERE NEWSHIFT_ID = P_NEWSHIFT_ID AND NEWSHIFT_DATE = P_NEWSHIFTDATE;
--向临时表插入数据INSERT INTO SPM_FOR_CENTER_SHIFTS_TMP(ROAD_NO,NEWSHIFT_ID,NEWSHIFT_DATE,TOTAL_COUNT,SENDED_COUNT,RETURN_FOR_EXCEPT,NO_RETURN_COUNT,RETURN_COUNT,DATA_NORMAL,DATA_BAD,DATA_PAY,DATA_NOPAY,CENTER_CLOSED)
SELECTORGAN_ID,SHIFT_ID,SHIFT_DATE,TOTAL_COUNT,(TOTAL_COUNT - UN_SEND_COUNT) AS SENDED_COUNT,(TOTAL_COUNT - UN_SEND_COUNT) AS RETURN_FOR_EXCEPT,SEND_COUNT AS NO_RETURN_COUNT,(DATA_NORMAL+DATA_BAD+DATA_PAY+DATA_NOPAY) AS RETURN_COUNT,DATA_NORMAL,DATA_BAD,DATA_PAY,DATA_NOPAY,CENTER_CLOSEDFROM(
SELECTORGAN_ID,MAX(NEWSHIFT_ID) AS SHIFT_ID,MAX(NEWSHIFT_DATE) AS SHIFT_DATE,SUM(TOTAL_COUNT) AS TOTAL_COUNT,SUM(UN_SEND_COUNT) AS UN_SEND_COUNT,SUM(SEND_COUNT) AS SEND_COUNT,SUM(DATA_NORMAL) AS DATA_NORMAL,SUM(DATA_BAD) AS DATA_BAD,SUM(DATA_PAY) AS DATA_PAY,SUM(DATA_NOPAY) AS DATA_NOPAY,CENTER_CLOSEDFROM(SELECT DISTINCT(OPERATOR_ID),ORGAN_ID,NEWSHIFT_ID,NEWSHIFT_DATE,( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS TOTAL_COUNT,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = -2 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS UN_SEND_COUNT,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = -1 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS SEND_COUNT,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = 0 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS DATA_NORMAL,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = 1 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS DATA_BAD,
( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = 2 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE ) AS DATA_PAY, ( SELECT COUNT(1) FROM BLS_DATA_STATUS_N T_A WHERE T_A.PROCESS_RESULT = 3 AND T_A.NEWSHIFT_ID = A.NEWSHIFT_ID AND T_A.OPERATOR_ID = A.OPERATOR_ID AND T_A.NEWSHIFT_DATE = A.NEWSHIFT_DATE
) AS DATA_NOPAY, A.CENTER_CLOSEDFROM SPM_FOR_BRANCH_SHIFTS AINNER JOIN ECDBA.BASE_ORGAN CON C.ORGAN_ID = A.ROAD_NOWHERE A.NEWSHIFT_ID = P_NEWSHIFT_IDAND A.NEWSHIFT_DATE = P_NEWSHIFTDATEAND C.ORGAN_LEVEL = 10) TMPGROUP BY ORGAN_ID,CENTER_CLOSED) T1;END;存储Oracleselect
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货