2023.01.13(RC300 ORACLE)

공부하기

거래처 조회 리스트 쿼리

-- 1. 접수내역 거래처 코드 기준 Group By 하되 GHS(공단검진)를 기본
 -- GHE(종합검진)이면서 공단검진 한 수검자들도 불러 모아야 함 (GRRVRMTCH 테이블에서 공단검진 패키지 구분이 있는 사람들)
 -- GRRVRMTCH 테이블에서 공단검진 패키지 구분이 있는 사람들
 -- 2. 거래처 코드가 없으면 지역가입자 이므로 코드 및 거래처명 공란으로 표시 
 -- 조회 조건 중 검진년도는 접수내역에 컬럼이 신설됨 
                  
SELECT * FROM WPPIMINFO                  
                 
SELECT * FROM GZCDMCPIF -- 거래처정보기본   

SELECT * FROM GRRVMOTPT -- 건강검진예약기본 이게 제일 핵심이 되는 테이블 1                  

SELECT * FROM GRRVRMTCH -- 건강검진맞춤세트내역 청구대상건수 같은 병원등록번호라도 패키지코드가 다른경우 패키지코드 종류 숫자로 청구대상건수로 매긴다. 숫자로 출력

SELECT * FROM GMCRMPCHK -- 수검자검진종류별검진진행상태관리 건강검진판정여부 N이면 미판정으로  N의 갯수로 미판정건수를 매긴다. 숫자로 출력

---------- 이부분은 정확히 해당 거래처를 클릭했을때 출력해주는 부분

WITH MTCH AS ( --첫번째 WITH문
	SELECT PATNO
	     , VIST_SN
	     , HE_DT
	     , SUBSTR(HE_BASIS_PKG_CD,1,2) HE_BASIS_PKG_CD
	  FROM GRRVRMTCH
	 WHERE HE_DT BETWEEN '20220101' AND '20221231' 
	 GROUP BY PATNO
	        , VIST_SN
	        , HE_DT
	        , SUBSTR(HE_BASIS_PKG_CD,1,2)
)
SELECT OTPT.PATNO
, OTPT.HE_ACTCUST_CD
, CPIF.ACTCUST_NM
, OTPT.HE_DT
   , (SELECT COUNT(T1.PATNO)
        FROM GMCRMPCHK T1 
       WHERE T1.PATNO = OTPT.PATNO
         AND T1.VIST_SN = OTPT.VIST_SN
         AND T1.HE_DT = OTPT.HE_DT
         AND T1.HE_JGMT_YN IS NULL) AS HE_JGMT_YN_CNT
   , COUNT(MTCH.HE_BASIS_PKG_CD) AS PKG_BLN_CNT
FROM GRRVMOTPT OTPT -- 건강검진예약기본
   , GZCDMCPIF CPIF  -- 거래처정보기본
   , MTCH
WHERE 1=1
  AND CPIF.HE_ACTCUST_CD(+) = OTPT.HE_ACTCUST_CD
  AND OTPT.HE_ACTCUST_CD(+) = '2013147'
  AND MTCH.PATNO = OTPT.PATNO
  AND MTCH.VIST_SN = OTPT.VIST_SN
  AND MTCH.HE_DT   = OTPT.HE_DT
  AND OTPT.HE_DT BETWEEN '20221011' AND '20221011' 
GROUP BY OTPT.PATNO
       , OTPT.VIST_SN
       , OTPT.HE_DT
       , OTPT.HE_ACTCUST_CD
       , CPIF.ACTCUST_NM
ORDER BY OTPT.PATNO;


    SELECT PATNO
	     , VIST_SN
	     , HE_DT
	     , SUBSTR(HE_BASIS_PKG_CD,1,2) HE_BASIS_PKG_CD
	  FROM GRRVRMTCH
	 WHERE HE_DT BETWEEN '20220101' AND '20221231' 
	   AND PATNO = '13375197'
       AND HE_DT = '20221011'
	 GROUP BY PATNO
	        , VIST_SN
	        , HE_DT
	        , SUBSTR(HE_BASIS_PKG_CD,1,2)
	        
	        
	        
	        
------------------------------------------------------------------------------------------------------------	        
	        ---=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 최종 거래처를 조회해주는 쿼리이다.
--SELECT HE_ACTCUST_CD
--  FROM (
SELECT HE_ACTCUST_CD
     , ACTCUST_NM
     , MAX_HE_DT
     , MIN_HE_DT
     , NVL((SELECT SUM(CNT)
          FROM (
        SELECT 1 AS CNT
          FROM GRRVRMTCH MTCH
             , GRRVMOTPT OTPT1
         WHERE OTPT1.PATNO   = MTCH.PATNO
           AND OTPT1.VIST_SN = MTCH.VIST_SN
           AND OTPT1.HE_DT   = MTCH.HE_DT
--           AND OTPT1.HE_ACTCUST_CD = '2013147'
           AND OTPT1.HE_ACTCUST_CD = MAIN.HE_ACTCUST_CD
           GROUP BY SUBSTR(MTCH.HE_BASIS_PKG_CD,1,2)
                  , MTCH.PATNO 
           )
       ),0) AS PKG_BLN_CNT
     , NVL(( SELECT SUM(COUNT(1)) AS NOT_JGMT_CNT
           FROM GMCRMPCHK PCHK
              , GRRVMOTPT OTPT2
          WHERE PCHK.HE_JGMT_YN IS NULL
            AND PCHK.PATNO = OTPT2.PATNO
            AND PCHK.VIST_SN = OTPT2.VIST_SN
            AND PCHK.HE_DT = OTPT2.HE_DT
--            AND OTPT2.HE_ACTCUST_CD = '2013147'
            AND OTPT2.HE_ACTCUST_CD = MAIN.HE_ACTCUST_CD
          GROUP BY PCHK.PATNO
                 , PCHK.VIST_SN
                 , PCHK.HE_DT 
     ),0) AS HE_JGMT_YN_CNT
  FROM (
SELECT OTPT.HE_ACTCUST_CD
     , CPIF.ACTCUST_NM
     , MAX(OTPT.HE_DT) AS MAX_HE_DT
     , MIN(OTPT.HE_DT) AS MIN_HE_DT
  FROM GRRVMOTPT OTPT
     , GZCDMCPIF CPIF
 WHERE 1=1
   AND OTPT.HE_DT BETWEEN '20200101' AND '20221231'
   AND OTPT.HE_ACTCUST_CD = '2013147'
   AND OTPT.HE_ACTCUST_CD = CPIF.HE_ACTCUST_CD(+)
   GROUP BY OTPT.HE_ACTCUST_CD
          , CPIF.ACTCUST_NM
   ) MAIN
--   ) 
--   WHERE PKG_BLN_CNT > 1
   ---=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
	
--------------------------쿼리검증---------------------------------------------   
   
SELECT SUM(count(MTCH.HE_BASIS_PKG_CD)) 
          FROM GRRVRMTCH MTCH
             , GRRVMOTPT OTPT
         WHERE OTPT.PATNO   = MTCH.PATNO
           AND OTPT.VIST_SN = MTCH.VIST_SN
           AND OTPT.HE_DT   = MTCH.HE_DT
--           AND OTPT.HE_ACTCUST_CD = MAIN.HE_ACTCUST_CD
           AND OTPT.HE_ACTCUST_CD = '2013147'
           GROUP BY HE_BASIS_PKG_CD
   
        SELECT SUM(CNT)
          FROM (
	        SELECT HE_BASIS_PKG_CD 
	             , PATNO
	             , COUNT (PATNO) CNT
	          FROM (
			        SELECT SUBSTR(HE_BASIS_PKG_CD,1,2) HE_BASIS_PKG_CD
			             , MTCH.PATNO
			          FROM GRRVRMTCH MTCH
			             , GRRVMOTPT OTPT
			         WHERE OTPT.PATNO   = MTCH.PATNO
			           AND OTPT.VIST_SN = MTCH.VIST_SN
			           AND OTPT.HE_DT   = MTCH.HE_DT
			--           AND OTPT.HE_ACTCUST_CD = MAIN.HE_ACTCUST_CD
			           AND OTPT.HE_ACTCUST_CD = '2013147'
			           GROUP BY SUBSTR(HE_BASIS_PKG_CD,1,2)
			                  , MTCH.PATNO
	             )
	           GROUP BY HE_BASIS_PKG_CD 
	             , PATNO
           )
   
           
           SELECT SUBSTR(HE_BASIS_PKG_CD,1,2) HE_BASIS_PKG_CD
             , MTCH.PATNO
          FROM GRRVRMTCH MTCH
             , GRRVMOTPT OTPT
         WHERE OTPT.PATNO   = MTCH.PATNO
           AND OTPT.VIST_SN = MTCH.VIST_SN
           AND OTPT.HE_DT   = MTCH.HE_DT
--           AND OTPT.HE_ACTCUST_CD = MAIN.HE_ACTCUST_CD
           AND OTPT.HE_ACTCUST_CD = '2013147'
           GROUP BY SUBSTR(HE_BASIS_PKG_CD,1,2)
                  , MTCH.PATNO
           
           COUNT(*) OVER (PARTITION BY 컬럼) 
           
           
   SELECT OTPT.HE_ACTCUST_CD
     , CPIF.ACTCUST_NM
     , MAX(OTPT.HE_DT) AS MAX_HE_DT
     , MIN(OTPT.HE_DT) AS MIN_HE_DT
  FROM GRRVMOTPT OTPT
     , GZCDMCPIF CPIF
 WHERE 1=1
   AND OTPT.HE_DT BETWEEN '20220101' AND '20221231'
   AND OTPT.HE_ACTCUST_CD = '2013147'
   AND OTPT.HE_ACTCUST_CD = CPIF.HE_ACTCUST_CD
   GROUP BY OTPT.HE_ACTCUST_CD
          , CPIF.ACTCUST_NM
          
          SELECT T1.PATNO
           , T1.VIST_SN
           , T1.HE_DT 
           , COUNT(T1.PATNO) AS NOT_JGMT_CNT
        FROM GMCRMPCHK T1 
       WHERE T1.HE_JGMT_YN IS NULL
         AND T1.HE_DT BETWEEN '20220101' AND '20221231' 
         AND T1.PATNO IN (
              SELECT PATNO
            FROM GRRVMOTPT
           WHERE HE_ACTCUST_CD = '2013147'
             AND HE_DT BETWEEN '20220101' AND '20221231' 
              )
         GROUP BY T1.PATNO
                , T1.VIST_SN
                , T1.HE_DT
          
                
                SELECT PATNO
	     , VIST_SN
	     , HE_DT
	     , SUBSTR(HE_BASIS_PKG_CD,1,2) HE_BASIS_PKG_CD
	  FROM GRRVRMTCH 
	 WHERE HE_DT BETWEEN '20220101' AND '20221231' 
	 AND PATNO IN (
              SELECT PATNO
            FROM GRRVMOTPT
           WHERE HE_ACTCUST_CD = '2013147'
             AND HE_DT BETWEEN '20220101' AND '20221231' 
              )
	 GROUP BY PATNO
	        , VIST_SN
	        , HE_DT
	        , SUBSTR(HE_BASIS_PKG_CD,1,2)
	        
	        
          SELECT PATNO
            FROM GRRVMOTPT
           WHERE HE_ACTCUST_CD = '2013147'
             AND HE_DT BETWEEN '20221011' AND '20221011' 
          
             SELECT *
               FROM GMCRMPCHK
              WHERE PATNO IN (
              SELECT PATNO
            FROM GRRVMOTPT
           WHERE HE_ACTCUST_CD = '2013147'
             AND HE_DT BETWEEN '20221011' AND '20221011' 
              )
              AND HE_JGMT_YN IS NULL
             
             SELECT PATNO
                  , SUBSTR(HE_BASIS_PKG_CD,1,2)
               FROM GRRVRMTCH
              WHERE HE_DT BETWEEN '20221011' AND '20221011' 
          GROUP BY PATNO
                 , SUBSTR(HE_BASIS_PKG_CD,1,2)