서비스별 | 회선서비스 | 계 | |||
사업자별 | 신규 | 변경 | 해지 | ||
KT | 1 | 0 | 0 | 1 | |
LGU+ | 1 | 1 | 1 | 3 | |
SK | 1 | 0 | 1 | 2 | |
SKB | 1 | 1 | 1 | 3 | |
계 | 4 | 2 | 3 | 9 |
WITH t AS ( SELECT 'KT' carrierid, '12/01' provisiondate, '' changedate, '' terminationdate FROM dual UNION ALL SELECT 'LGU+', '12/01', '' , '' FROM dual UNION ALL SELECT 'LGU+', '12/01', '12/02', '' FROM dual UNION ALL SELECT 'LGU+', '12/01', '12/02', '12/03' FROM dual UNION ALL SELECT 'SK' , '12/01', '' , '' FROM dual UNION ALL SELECT 'SK' , '12/01', '' , '12/02' FROM dual UNION ALL SELECT 'SKB' , '12/01', '' , '' FROM dual UNION ALL SELECT 'SKB' , '12/01', '12/02', '' FROM dual UNION ALL SELECT 'SKB' , '12/01', '12/02', '12/03' FROM dual ) SELECT carrierid , NVL(SUM(DECODE(gb, 1, cnt)), 0) 신규 , NVL(SUM(DECODE(gb, 2, cnt)), 0) 변경 , NVL(SUM(DECODE(gb, 3, cnt)), 0) 해지 , SUM(cnt) 계 FROM (SELECT carrierid , DECODE('', terminationdate, DECODE('', changedate, 1, 2), 3) gb , COUNT(*) cnt FROM t GROUP BY carrierid , DECODE('', terminationdate, DECODE('', changedate, 1, 2), 3) ) GROUP BY ROLLUP(carrierid) ;
WITH T (CARRIERID ,PROVISIONDATE, CHANGEDATE ,TERMINATIONDATE) AS ( SELECT 'KT' ,'20130101','','20130101' FROM DUAL UNION ALL SELECT 'KT' ,'20130101','20130101','' FROM DUAL UNION ALL SELECT 'LGU+','20130101','','' FROM DUAL UNION ALL SELECT 'LGU+','20130101','','' FROM DUAL UNION ALL SELECT 'SK' ,'20130101','','' FROM DUAL UNION ALL SELECT 'SKB' ,'20130101','20130101','20130101' FROM DUAL ) SELECT CARRIERID , SUM(신규) 신규 , SUM(변경) 변경 , SUM(해지) 해지 , SUM(계) 계 FROM (SELECT CARRIERID , COUNT(CASE WHEN CHANGEDATE IS NULL AND TERMINATIONDATE IS NULL THEN 1 END) 신규, COUNT(CASE WHEN CHANGEDATE IS NOT NULL AND TERMINATIONDATE IS NULL THEN 1 END) 변경 , COUNT(CASE WHEN TERMINATIONDATE IS NOT NULL THEN 1 END) 해지 , COUNT(*) 계 FROM T GROUP BY CARRIERID ) GROUP BY ROLLUP (( CARRIERID , 신규 , 변경 , 해지 , 계))