꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 양식구성? 문의드립니다

by 동동동 [SQL Query] [2020.03.18 12:03:17]

안녕하세요..쿼리 문의 드립니다..

원자료가

/* 예약병실 마스터 */
WARD	ROOM	BED
A	001	01
A	001	02
A	001	03
A	002	01
A	002	02
B	001	01
B	001	02
B	001	03

/* 예약자 정보 */
AID	FDY	FTM	TDY	WARD	ROOM	BED
1111111	20200301	1000	20200304	A	001	01
2222222	20200303	1000	20200307	A	001	01
3333333	20200309	1500	20200320	A	001	01
4444444	20200305	1500	20200307	B	001	03

이렇게 있을때..해당 쿼리 문으로..

WARD	ROOM	BED	AM_20200301	PM_20200301	AM_20200302	PM_20200302	AM_20200303	PM_20200303	AM_20200304	PM_20200304	AM_20200305	PM_20200305	AM_20200306	PM_20200306	AM_20200307	PM_20200307	AM_20200308	PM_20200308	AM_20200309	PM_20200309	AM_20200310	PM_20200310
A	001	01	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304
A	001	01					2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307
A	001	01																	3333333,20200309,1500,20200320	3333333,20200309,1500,20200320	3333333,20200309,1500,20200320	3333333,20200309,1500,20200320
A	001	02
A	001	03
A	002	01
A	002	02
B	001	01
B	001	02
B	001	03									4444444,20200305,1500,20200307	4444444,20200305,1500,20200307	4444444,20200305,1500,20200307	4444444,20200305,1500,20200307	4444444,20200305,1500,20200307	4444444,20200305,1500,20200307

이렇게 결과를 냈는데요..

노랗게 칠해진 저부분을 해당 일자에 중복된 예약자가 없기 때문에 첫줄에 표시하고 싶은데요..

WARD	ROOM	BED	AM_20200301	PM_20200301	AM_20200302	PM_20200302	AM_20200303	PM_20200303	AM_20200304	PM_20200304	AM_20200305	PM_20200305	AM_20200306	PM_20200306	AM_20200307	PM_20200307	AM_20200308	PM_20200308	AM_20200309	PM_20200309	AM_20200310	PM_20200310
A	001	01	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304	1111111,20200301,1000,20200304									3333333,20200309,1500,20200320	3333333,20200309,1500,20200320	3333333,20200309,1500,20200320	3333333,20200309,1500,20200320
A	001	01					2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307	2222222,20200303,1000,20200307
A	001	02
A	001	03
A	002	01
A	002	02
B	001	01
B	001	02
B	001	03									4444444,20200305,1500,20200307	4444444,20200305,1500,20200307	4444444,20200305,1500,20200307	4444444,20200305,1500,20200307	4444444,20200305,1500,20200307	4444444,20200305,1500,20200307

방법이 있을까요??

/* 예약병실 마스터 */
WITH WARD_MST AS (
SELECT 'A' WARD, '001' ROOM, '01' BED FROM DUAL UNION ALL
SELECT 'A' WARD, '001' ROOM, '02' BED FROM DUAL UNION ALL
SELECT 'A' WARD, '001' ROOM, '03' BED FROM DUAL UNION ALL
SELECT 'A' WARD, '002' ROOM, '01' BED FROM DUAL UNION ALL
SELECT 'A' WARD, '002' ROOM, '02' BED FROM DUAL UNION ALL
SELECT 'B' WARD, '001' ROOM, '01' BED FROM DUAL UNION ALL
SELECT 'B' WARD, '001' ROOM, '02' BED FROM DUAL UNION ALL
SELECT 'B' WARD, '001' ROOM, '03' BED FROM DUAL
),
/* 예약자 정보 */
ADM_INF AS (
SELECT '1111111' AID, '20200301' FDY, '1000' FTM, '20200304' TDY, 'A' WARD, '001' ROOM, '01' BED FROM DUAL UNION ALL
SELECT '2222222' AID, '20200303' FDY, '1000' FTM, '20200307' TDY, 'A' WARD, '001' ROOM, '01' BED FROM DUAL UNION ALL
SELECT '3333333' AID, '20200309' FDY, '1500' FTM, '20200320' TDY, 'A' WARD, '001' ROOM, '01' BED FROM DUAL UNION ALL
SELECT '4444444' AID, '20200305' FDY, '1500' FTM, '20200307' TDY, 'B' WARD, '001' ROOM, '03' BED FROM DUAL
)
SELECT A.WARD    /* 병동 */
     , A.ROOM    /* 병실 */
     , A.BED     /* 병상 */
     , CASE WHEN ('20200301' BETWEEN B.FDY AND B.TDY) OR (('20200301' = B.FDY OR '20200301' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200301
     , CASE WHEN ('20200301' BETWEEN B.FDY AND B.TDY) OR (('20200301' = B.FDY OR '20200301' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200301
     , CASE WHEN ('20200302' BETWEEN B.FDY AND B.TDY) OR (('20200302' = B.FDY OR '20200302' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200302
     , CASE WHEN ('20200302' BETWEEN B.FDY AND B.TDY) OR (('20200302' = B.FDY OR '20200302' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200302
     , CASE WHEN ('20200303' BETWEEN B.FDY AND B.TDY) OR (('20200303' = B.FDY OR '20200303' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200303
     , CASE WHEN ('20200303' BETWEEN B.FDY AND B.TDY) OR (('20200303' = B.FDY OR '20200303' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200303
     , CASE WHEN ('20200304' BETWEEN B.FDY AND B.TDY) OR (('20200304' = B.FDY OR '20200304' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200304
     , CASE WHEN ('20200304' BETWEEN B.FDY AND B.TDY) OR (('20200304' = B.FDY OR '20200304' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200304
     , CASE WHEN ('20200305' BETWEEN B.FDY AND B.TDY) OR (('20200305' = B.FDY OR '20200305' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200305
     , CASE WHEN ('20200305' BETWEEN B.FDY AND B.TDY) OR (('20200305' = B.FDY OR '20200305' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200305
     , CASE WHEN ('20200306' BETWEEN B.FDY AND B.TDY) OR (('20200306' = B.FDY OR '20200306' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200306
     , CASE WHEN ('20200306' BETWEEN B.FDY AND B.TDY) OR (('20200306' = B.FDY OR '20200306' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200306
     , CASE WHEN ('20200307' BETWEEN B.FDY AND B.TDY) OR (('20200307' = B.FDY OR '20200307' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200307
     , CASE WHEN ('20200307' BETWEEN B.FDY AND B.TDY) OR (('20200307' = B.FDY OR '20200307' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200307
     , CASE WHEN ('20200308' BETWEEN B.FDY AND B.TDY) OR (('20200308' = B.FDY OR '20200308' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200308
     , CASE WHEN ('20200308' BETWEEN B.FDY AND B.TDY) OR (('20200308' = B.FDY OR '20200308' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200308
     , CASE WHEN ('20200309' BETWEEN B.FDY AND B.TDY) OR (('20200309' = B.FDY OR '20200309' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200309
     , CASE WHEN ('20200309' BETWEEN B.FDY AND B.TDY) OR (('20200309' = B.FDY OR '20200309' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200309
     , CASE WHEN ('20200310' BETWEEN B.FDY AND B.TDY) OR (('20200310' = B.FDY OR '20200310' = TDY) AND (B.FTM BETWEEN '0000' AND '1300')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END AM_20200310
     , CASE WHEN ('20200310' BETWEEN B.FDY AND B.TDY) OR (('20200310' = B.FDY OR '20200310' = TDY) AND (B.FTM BETWEEN '1301' AND '2359')) THEN
             DECODE(AID, NULL, '', AID||','||FDY||','||FTM||','||TDY)
            ELSE
             ''
        END PM_20200310        
  FROM WARD_MST A /* 예약병실 마스터 */
     , ADM_INF B      /* 예약자 정보 */
WHERE A.WARD = B.WARD(+)
  AND A.ROOM = B.ROOM(+)
  AND A.BED  = B.BED(+)
  AND B.FDY(+) <= '20200331'
  AND B.TDY(+) >= '20200301'
ORDER BY A.WARD, A.ROOM, A.BED
;
현재 작성하고 있는 쿼리 입니다..

도움 부탁 드립니다...

by 마농 [2020.03.18 12:24:43]

1. 중복 예약?
- 침대 하나 놓고 환자들끼리 싸움 나겠는데요?
- 애초에 이런 중복자료가 들어오면 안될 것 같은데요?
2. 너무 많은 정보를 담고 있는 것 아닌가요?
- ID 만 표현해도 될 듯 한데요?
- 날짜는 이미 타이틀로 표시가 되고 있고
3. 시간정보 및 오전/오후
- 입실시간은 첫날에만 의미 있는 것 아닌지?
- 퇴실시간은 없네요?
- 오전/오후 나누려면 퇴실 시간도 필요할 듯 한데요?

by 동동동 [2020.03.18 13:48:37]

마농님 답글 감사드립니다..

1. 중복예약인 경우 먼저 예약된 환자가 그전에 나갈수 있기 때문에 현재 조금 날짜가 겹치게 잡을수도 있습니다.(거의 그전에 나갑니다..)

2. 예..정보는 조절할 수 있습니다..일단은 데이타 확인 차원에서 저렇게 표시해 보고 있었습니다.

3. 예약현황이기 때문에 퇴실시간은 현재 관리하고 있지 않습니다.

관심 감사드립니다..

by 마농 [2020.03.18 15:56:19]

WITH ward_mst AS
( /* 예약병실 마스터 */
SELECT 'A' ward, '001' room, '01' bed FROM dual
UNION ALL SELECT 'A', '001', '02' FROM dual
UNION ALL SELECT 'A', '001', '03' FROM dual
UNION ALL SELECT 'A', '002', '01' FROM dual
UNION ALL SELECT 'A', '002', '02' FROM dual
UNION ALL SELECT 'B', '001', '01' FROM dual
UNION ALL SELECT 'B', '001', '02' FROM dual
UNION ALL SELECT 'B', '001', '03' FROM dual
)
, adm_inf AS
( /* 예약자 정보 */
SELECT '1111111' aid, '20200301' fdy, '1000' ftm, '20200304' tdy, 'A' ward, '001' room, '01' bed FROM dual
UNION ALL SELECT '2222222', '20200303', '1000', '20200307', 'A', '001', '01' FROM dual
UNION ALL SELECT '3333333', '20200309', '1500', '20200320', 'A', '001', '01' FROM dual
UNION ALL SELECT '4444444', '20200305', '1500', '20200307', 'B', '001', '03' FROM dual
)
, search_ym AS
( /* 검색 년월 조건 */
SELECT dt
     , ap ||'_'|| SUBSTR(dt, 7) cd
     , dt||stm sdt
     , dt||etm edt
  FROM (SELECT TO_CHAR(dt + LEVEL - 1, 'yyyymmdd') dt
          FROM (SELECT TO_DATE('202003', 'yyyymm') dt FROM dual)
         CONNECT BY LEVEL <= LAST_DAY(dt) - dt + 1
        )
     , (SELECT 'AM' ap, '0000' stm, '1259' etm FROM dual
        UNION ALL SELECT 'PM', '1300', '2359' FROM dual
        )
)
, tmp AS
( /* 중복 환자 번호 부여 */
SELECT aid, fdy, ftm, tdy
     , fdy || ftm    sdt
     , tdy || '2359' edt
     , ward, room, bed
     , aid ||','|| fdy ||','|| ftm ||','|| tdy v
     , ROW_NUMBER() OVER(PARTITION BY ward, room, bed, grp ORDER BY fdy, ftm, tdy) rn
  FROM (SELECT aid, fdy, ftm, tdy, ward, room, bed
             , SUM(flag) OVER(PARTITION BY ward, room, bed ORDER BY fdy, ftm, tdy) grp
          FROM (SELECT aid, fdy, ftm, tdy, ward, room, bed
                     , CASE WHEN MAX(tdy) OVER(PARTITION BY ward, room, bed ORDER BY fdy, ftm, tdy
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= fdy
                            THEN 0 ELSE 1 END flag
                  FROM adm_inf
                 WHERE fdy <= '20200331'
                   AND tdy >= '20200301'
                )
        )
)
SELECT ward, room, bed, rn
     , MIN(DECODE(cd, 'AM_01', v)) am_01, MIN(DECODE(cd, 'PM_01', v)) pm_01
     , MIN(DECODE(cd, 'AM_02', v)) am_02, MIN(DECODE(cd, 'PM_02', v)) pm_02
     , MIN(DECODE(cd, 'AM_03', v)) am_03, MIN(DECODE(cd, 'PM_03', v)) pm_03
     , MIN(DECODE(cd, 'AM_04', v)) am_04, MIN(DECODE(cd, 'PM_04', v)) pm_04
     , MIN(DECODE(cd, 'AM_05', v)) am_05, MIN(DECODE(cd, 'PM_05', v)) pm_05
     , MIN(DECODE(cd, 'AM_06', v)) am_06, MIN(DECODE(cd, 'PM_06', v)) pm_06
     , MIN(DECODE(cd, 'AM_07', v)) am_07, MIN(DECODE(cd, 'PM_07', v)) pm_07
     , MIN(DECODE(cd, 'AM_08', v)) am_08, MIN(DECODE(cd, 'PM_08', v)) pm_08
     , MIN(DECODE(cd, 'AM_09', v)) am_09, MIN(DECODE(cd, 'PM_09', v)) pm_09
     , MIN(DECODE(cd, 'AM_10', v)) am_10, MIN(DECODE(cd, 'PM_10', v)) pm_10
     , MIN(DECODE(cd, 'AM_11', v)) am_11, MIN(DECODE(cd, 'PM_11', v)) pm_11
     , MIN(DECODE(cd, 'AM_12', v)) am_12, MIN(DECODE(cd, 'PM_12', v)) pm_12
     , MIN(DECODE(cd, 'AM_13', v)) am_13, MIN(DECODE(cd, 'PM_13', v)) pm_13
     , MIN(DECODE(cd, 'AM_14', v)) am_14, MIN(DECODE(cd, 'PM_14', v)) pm_14
     , MIN(DECODE(cd, 'AM_15', v)) am_15, MIN(DECODE(cd, 'PM_15', v)) pm_15
     , MIN(DECODE(cd, 'AM_16', v)) am_16, MIN(DECODE(cd, 'PM_16', v)) pm_16
     , MIN(DECODE(cd, 'AM_17', v)) am_17, MIN(DECODE(cd, 'PM_17', v)) pm_17
     , MIN(DECODE(cd, 'AM_18', v)) am_18, MIN(DECODE(cd, 'PM_18', v)) pm_18
     , MIN(DECODE(cd, 'AM_19', v)) am_19, MIN(DECODE(cd, 'PM_19', v)) pm_19
     , MIN(DECODE(cd, 'AM_20', v)) am_20, MIN(DECODE(cd, 'PM_20', v)) pm_20
     , MIN(DECODE(cd, 'AM_21', v)) am_21, MIN(DECODE(cd, 'PM_21', v)) pm_21
     , MIN(DECODE(cd, 'AM_22', v)) am_22, MIN(DECODE(cd, 'PM_22', v)) pm_22
     , MIN(DECODE(cd, 'AM_23', v)) am_23, MIN(DECODE(cd, 'PM_23', v)) pm_23
     , MIN(DECODE(cd, 'AM_24', v)) am_24, MIN(DECODE(cd, 'PM_24', v)) pm_24
     , MIN(DECODE(cd, 'AM_25', v)) am_25, MIN(DECODE(cd, 'PM_25', v)) pm_25
     , MIN(DECODE(cd, 'AM_26', v)) am_26, MIN(DECODE(cd, 'PM_26', v)) pm_26
     , MIN(DECODE(cd, 'AM_27', v)) am_27, MIN(DECODE(cd, 'PM_27', v)) pm_27
     , MIN(DECODE(cd, 'AM_28', v)) am_28, MIN(DECODE(cd, 'PM_28', v)) pm_28
     , MIN(DECODE(cd, 'AM_29', v)) am_29, MIN(DECODE(cd, 'PM_29', v)) pm_29
     , MIN(DECODE(cd, 'AM_30', v)) am_30, MIN(DECODE(cd, 'PM_30', v)) pm_30
     , MIN(DECODE(cd, 'AM_31', v)) am_31, MIN(DECODE(cd, 'PM_31', v)) pm_31
  FROM (SELECT a.ward, a.room, a.bed
             , NVL(c.rn, 1) rn
             , b.cd
             , c.v
          FROM ward_mst a
         CROSS JOIN search_ym b
          LEFT OUTER JOIN tmp c
            ON a.ward = c.ward
           AND a.room = c.room
           AND a.bed  = c.bed
           AND b.sdt <= c.edt
           AND b.edt >= c.sdt 
        )
 GROUP BY ward, room, bed, rn
 ORDER BY ward, room, bed, rn
;

by 동동동 [2020.03.18 17:35:25]

마농님 정말 감사드립니다..

감탄만 나오네요...정말 실력이 너무 부럽고..감사드립니다...

by 마농 [2020.03.18 18:03:08]

입실시간 처리를 빼먹어서 쿼리 수정했습니다.

by 아발란체 [2020.03.18 17:41:25]

얼... 코로나19 관련 쿼리... ?

농담입니다..

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변