데이터베이스 질문과 답변

항목별 총계와 합쳐진 문자열에 대한 질문 0 1 2,735

by DONKEY [SQL Query] LISTAGG MIN PIVOT [2016.06.08 09:05:23]

캡처.PNG (15,856Bytes)

WITH ROOM AS(

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
     SELECT C.WARD
                 , SUM(C.BED_CNT) AS BED_CNT
                 , (SELECT COUNT(A.AFF_CHOS_NO)
                       FROM OCS.PIA_ADMI_INFO        A
                                , OCS.PIA_CHG_ROOM_CLN_HIS B
                     WHERE 1 = 1
                          AND A.AD_STUS_GB IN ('A','D','T')
                          AND A.MAIN_SUB_GB = 1
                          AND A.AFF_CHOS_NO = B.AFF_CHOS_NO
                          AND A.AD_STUS_GB  = B.AD_STUS_GB
                          AND TO_CHAR(SYSDATE,'YYYYMMDD') >= B.STR_YMD
                          AND TO_CHAR(SYSDATE,'YYYYMMDD') <= B.END_YMD
                          AND B.WARD = C.WARD  ) AS PAT_CNT  
      FROM OCS.PBB_WARD_ROOM_MST C
    WHERE TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN C.STR_YMD AND C.END_YMD
GROUP BY C.WARD),
DEPT AS(
  SELECT A.DUT_DEPT_CD
     ,  (SELECT DEPT_NM FROM OCS.ZMM_DEPT B WHERE B.DEPT_CD = A.DUT_DEPT_CD) DUT_DEPT_NM
     ,  A.DEPT_CNT
     ,  A.NUR
     ,  A.NUR_AIDE
   FROM (SELECT A.DUT_DEPT_CD
      , COUNT(*) DEPT_CNT
      , DECODE(MIN(B.OCP_TYPE_CD),'022',LISTAGG(B.KOR_NM,'/') WITHIN GROUP (ORDER BY A.RANKING)) AS NUR
      , DECODE(MIN(B.OCP_TYPE_CD),'081',LISTAGG(B.KOR_NM,'/') WITHIN GROUP (ORDER BY A.RANKING)) AS NUR_AIDE
           FROM MID_DUTYSCHEDULE A
              , MIJ_EMPLOYE B
          WHERE A.EMP_NO =B .EMP_NO
            AND A.DUT_GB_CD = '36'
            AND A.DUT_YMD = '20150101'
       GROUP BY A.DUT_DEPT_CD
       ORDER BY DEPT_CNT) A)
     SELECT B.DUT_DEPT_NM
          , A.BED_CNT
          , A.PAT_CNT
          , B.DEPT_CNT
          , B.NUR
          , B.NUR_AIDE
       FROM ROOM A 
          , DEPT B
      WHERE A.WARD(+) = B.DUT_DEPT_CD

안녕하세요 회원님들 아침에 비가 내려 상쾌한 아침입니당 ㅎㅎㅎ
오늘도 제가 골머리 썩히고 있는 문젯거리를 들고 찾아왔습니다.
오늘 제가 여쭤볼것은 침상,환자,총근무자수의 SUM과
'/'로 합쳐진 근무자들의 수를 구하고 싶습니다.
아 그리고 LISTAGG에서 GROUP BY MIN으로 같은 직종별로
묶어서 출력 하고있는데 여기서 MIN을 하면 제가 원하는데이터가 나오고
MAX를 하면 이상한 데이터가 나오네용.. 그것도 왜그런지 궁금합니당 

결과 셋은

by DONKEY [2016.06.08 10:55:41]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
WITH ROOM AS(
     SELECT C.WARD
                 , SUM(C.BED_CNT) AS BED_CNT
                 , (SELECT COUNT(A.AFF_CHOS_NO)
                       FROM OCS.PIA_ADMI_INFO        A
                                , OCS.PIA_CHG_ROOM_CLN_HIS B
                     WHERE 1 = 1
                          AND A.AD_STUS_GB IN ('A','D','T')
                          AND A.MAIN_SUB_GB = 1
                          AND A.AFF_CHOS_NO = B.AFF_CHOS_NO
                          AND A.AD_STUS_GB  = B.AD_STUS_GB
                          AND TO_CHAR(SYSDATE,'YYYYMMDD') >= B.STR_YMD
                          AND TO_CHAR(SYSDATE,'YYYYMMDD') <= B.END_YMD
                          AND B.WARD = C.WARD  ) AS PAT_CNT  
      FROM OCS.PBB_WARD_ROOM_MST C
    WHERE TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN C.STR_YMD AND C.END_YMD
GROUP BY C.WARD),
DEPT AS(
     SELECT A.DUT_DEPT_CD
               ,  (SELECT DEPT_NM FROM OCS.ZMM_DEPT B WHERE B.DEPT_CD = A.DUT_DEPT_CD) DUT_DEPT_NM
               ,  A.DEPT_CNT
               ,  A.NUR
               ,  A.NUR_AIDE
               ,  A.NUR_CNT
               ,  A.NUR_AIDE_CNT
      FROM(SELECT A.DUT_DEPT_CD
                             , COUNT(*) DEPT_CNT
                             , DECODE(MIN(B.OCP_TYPE_CD),'022',COUNT(B.OCP_TYPE_CD))  AS NUR_CNT
                             , DECODE(MIN(B.OCP_TYPE_CD),'081',COUNT(B.OCP_TYPE_CD))  AS NUR_AIDE_CNT
                             , DECODE(MIN(B.OCP_TYPE_CD),'022',LISTAGG(B.KOR_NM,'/') WITHIN GROUP (ORDER BY A.RANKING)) AS NUR
                             , DECODE(MIN(B.OCP_TYPE_CD),'081',LISTAGG(B.KOR_NM,'/') WITHIN GROUP (ORDER BY A.RANKING)) AS NUR_AIDE
                    FROM MID_DUTYSCHEDULE A
                             , MIJ_EMPLOYE B
                  WHERE A.EMP_NO =B .EMP_NO
                       AND A.DUT_GB_CD = I_DUT_GB_CD
                       AND A.DUT_YMD = I_DUT_YMD
             GROUP BY A.DUT_DEPT_CD
              ORDER BY DEPT_CNT) A)
     SELECT DECODE(GROUPING(B.DUT_DEPT_NM),1,'[합계]',B.DUT_DEPT_NM) DUT_DEPT_NM
                 , NVL(SUM(A.BED_CNT),0) BED_CNT
                 , NVL(SUM(A.PAT_CNT),0) PAT_CNT
                 , NVL(SUM(B.DEPT_CNT),0) DEPT_CNT
                 , DECODE(GROUPING(B.DUT_DEPT_NM),1,'간호사계 : ' || NVL(SUM(B.NUR_CNT),0),B.NUR) NUR
                 , DECODE(GROUPING(B.DUT_DEPT_NM),1,'조무사계 : ' || NVL(SUM(B.NUR_AIDE_CNT),0),B.NUR_AIDE) NUR_AIDE
                 , GROUPING_ID(B.DUT_DEPT_NM,B.NUR) G_ID
        FROM ROOM A 
                 , DEPT B
      WHERE A.WARD(+) = B.DUT_DEPT_CD
GROUP BY GROUPING SETS((B.DUT_DEPT_NM,B.NUR,B.NUR_AIDE),(B.DUT_DEPT_NM),())
HAVING GROUPING_ID(B.DUT_DEPT_NM,B.NUR) <> 1
ORDER BY DEPT_CNT
 
==자답으로 일딴 원하는 데이터를 추출하는데는 성공했는데,,MIN과MAX를 썼을때
값이 틀려서.. 어거지 코드 같아서 제대로된 쿼리를 짜려면 어떤식으로 해야되는지좀
알려주세요 ...MIN,MAX는 문자열 합칠때와 직종별 카운트낼때 입니당

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입