데이터베이스 질문과 답변
안녕하세요.
| OFF_ID | SERV_ID | SEQ_NO | GUBUN | SD_DT |
| 9772 | 000001 | 1 | 1 | 20160601 |
| 9772 | 000001 | 2 | 2 | 20160601 |
| 9772 | 000001 | 3 | 4 | 20160613 |
| 9772 | 000002 | 1 | 1 | 20160601 |
| 9772 | 000002 | 2 | 3 | 20160603 |
| 9772 | 000003 | 1 | 1 | 20160601 |
| 9772 | 000003 | 2 | 2 | 20160607 |
| 10295 | 000001 | 1 | 1 | 20160613 |
| 10295 | 000001 | 2 | 4 | 20160613 |
off_id 거래처 코드인데 serv_id 상담 현황 회차입니다. 그리고 seq_no 가 serv_id 별 세부 면담 횟수 입니다.
gubun = 4 는 상담 종결 입니다.
거래처별 종결되지 않은 상담이 몇건인지 cnt 를 구하고 싶습니다.
off_id = '9772', serv_id = '000001' 은 gubun = '4' 가 있어서 카운트에서 제외
serv_id = '000002', serv_id = '000003' 은 gubun = '4' 가 없으니
off_id = '9772' 상담현황은 '2건' 임을 구할 수 있을까요? 그리고 sd_dt 를 가져와서 오늘날짜 -1 일인건 건수 옆에 new 표시를 해서 최근에 상담내역을 등록했음을 표시하고자 합니다.
저번주 부터 고민하다 결국 이렇게 도움을 받고자 문의 드립니다.
by jkson
[2016.06.14 10:03:32]
WITH T AS ( SELECT '9772' AS OFF_ID,'1' AS SERV_ID,'1' AS SEQ_NO,'1' AS GUBUN,'20160601' AS SD_DT FROM DUAL UNION ALL SELECT '9772' AS C0,'1' AS C1,'2' AS C2,'2' AS C3,'20160601' AS C4 FROM DUAL UNION ALL SELECT '9772' AS C0,'1' AS C1,'3' AS C2,'4' AS C3,'20160613' AS C4 FROM DUAL UNION ALL SELECT '9772' AS C0,'2' AS C1,'1' AS C2,'1' AS C3,'20160601' AS C4 FROM DUAL UNION ALL SELECT '9772' AS C0,'2' AS C1,'2' AS C2,'3' AS C3,'20160603' AS C4 FROM DUAL UNION ALL SELECT '9772' AS C0,'3' AS C1,'1' AS C2,'1' AS C3,'20160601' AS C4 FROM DUAL UNION ALL SELECT '9772' AS C0,'3' AS C1,'2' AS C2,'2' AS C3,'20160607' AS C4 FROM DUAL UNION ALL SELECT '10295' AS C0,'1' AS C1,'1' AS C2,'1' AS C3,'20160613' AS C4 FROM DUAL UNION ALL SELECT '10295' AS C0,'1' AS C1,'2' AS C2,'4' AS C3,'20160613' AS C4 FROM DUAL ) SELECT * FROM ( SELECT OFF_ID, SERV_ID, SEQ_NO, GUBUN, SD_DT , RANK() OVER (PARTITION BY OFF_ID, SERV_ID ORDER BY SEQ_NO DESC) RN , DECODE(MAX(GUBUN) OVER (PARTITION BY OFF_ID, SERV_ID),'4','Y','N') CLOSEYN , CASE WHEN TO_DATE(SD_DT,'YYYYMMDD') >= TRUNC(SYSDATE) - 1 THEN 'NEW' ELSE '' END NEWYN FROM T ) WHERE RN = 1 AND CLOSEYN = 'N'
by 보일듯말듯
[2016.06.14 11:51:18]
감사합니다.
일주일 고민을 한방에 해결해 주셔서 ^^
적어주신 쿼리를 연구해 보도록 하겠습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.