2021.11.30 ㅈㅂㅇ

2021.11.30 해지 고객 처리

 

1. Entry의 해지 회선수 >= 등록된 총 회선수 (UseFlag == 'Y')

 

SELECT D.CompId, D.총회선수, C.해지해제회선
FROM (SELECT D.CompId, COUNT(D.EntryId) AS '총회선수'
FROM BP_PRODENTRY D
WHERE D.UseFlag = 'Y'
GROUP BY D.CompId
) D
LEFT JOIN
(SELECT C.CompId, COUNT(Case When C.ProgState = 4 then 1 When C.ProgState = 5 then 1 end) as '해지해제회선'
FROM BP_PRODENTRY C
WHERE C.UseFlag = 'Y'
GROUP BY C.CompId
) C
ON D.CompId = C.CompId
WHERE C.해지해제회선 >= D.총회선수
GROUP BY D.CompId

 

2. 해당 CompId의 BP_COMPANY일부 칼럼 Update 처리

7730건 01:15 소요

 

UPDATE BP_COMPANY 
SET 
ModReason = 'D.'
WHERE CompId IN (SELECT CONCAT(bp_comp.CompId, ',') FROM
                    (SELECT A.CompId
                      FROM BP_COMPANY A
                      RIGHT JOIN(
                                SELECT D.CompId
                                FROM (SELECT D.CompId, COUNT(D.EntryId) AS '총회선수'
                                      FROM BP_PRODENTRY D
                                      WHERE D.UseFlag = 'Y'
                                      GROUP BY D.CompId
                                     ) D
                                LEFT JOIN
                                      (SELECT C.CompId, COUNT(Case When C.ProgState = 4 then 1 When C.ProgState = 5 then 1 end) as '해지해제회선'
                                       FROM BP_PRODENTRY C
                                       WHERE C.UseFlag = 'Y'
                                       GROUP BY C.CompId
                                       ) C
                                ON D.CompId = C.CompId
                                WHERE C.해지해제회선 >= D.총회선수
                                GROUP BY D.CompId
                                ) B
                     ON A.CompId = B.CompId
                     ) bp_comp
                 )

 

 

3.  entry 정보 delete

25042건 04:39 소요

 

DELETE 
FROM BP_PRODENTRY 
WHERE CompId IN (
                SELECT CONCAT(bp_CountEntry.CompId, ',')
                FROM
                    (
                    SELECT D.CompId, D.총회선수, C.해지해제회선
                    FROM (SELECT D.CompId, COUNT(D.EntryId) AS '총회선수'
                          FROM BP_PRODENTRY D
                          WHERE D.UseFlag = 'Y'
                          GROUP BY D.CompId
                          ) D
                    LEFT JOIN
                          (SELECT C.CompId, COUNT(Case When C.ProgState = 4 then 1 When C.ProgState = 5 then 1 end) as '해지해제회선'
                           FROM BP_PRODENTRY C
                           WHERE C.UseFlag = 'Y'
                           GROUP BY C.CompId
                           ) C
                    ON D.CompId = C.CompId
                    WHERE C.해지해제회선 >= D.총회선수
                    GROUP BY D.CompId
                    ) bp_CountEntry
                )