쿼리 질문(마농님께서 조언해주신거에서 변경하다가 질문이 생겼습니다.) 0 4 1,000

by 개발고수가되기를 [SQL Query] [2019.08.27 18:12:39]


            WITH DATA_T AS
            (
	         SELECT YMD_FR, MAX(YMD_TO) YMD_TO
	               ,MAX(DECODE(MEMO_NO, 1, NM_USER, '')) AS TTL_EVENT1
	               ,MAX(DECODE(MEMO_NO, 2, NM_USER, '')) AS TTL_EVENT2
	               ,MAX(DECODE(MEMO_NO, 3, NM_USER, '')) AS TTL_EVENT3
	           FROM(
	                SELECT M.CD_USER, FN_GET_EMP_NM(M.CD_USER) NM_USER, D.YMD_FR, D.YMD_TO, RANK() OVER(PARTITION BY  D.YMD_FR ORDER BY M.CD_USER DESC) AS MEMO_NO
	                  FROM T_VAC_210 M , T_VAC_220 D , T_VAC_100 C , T_VAC_110 S , T_EMP_100 E
	                 WHERE M.YMD_REQ = D.YMD_REQ
	                   AND M.CD_USER = D.CD_USER
	                   AND M.REQ_NO = D.REQ_NO
	                   AND M.CD_USER = E.CD_USER
	                   AND D.CD_VAC = C.CD_VAC
	                   AND D.CD_VAC = S.CD_VAC(+)
	                   AND D.CD_VAC_D = S.CD_VAC_D(+)
	                   AND D.YMD_FR LIKE '201702'||'%'
                    )
             GROUP BY YMD_FR
             )
          , CALENDAR_T AS
          (
           SELECT TO_CHAR(DT + LEVEL - 1, 'YYYYMMDD') DT
             FROM (SELECT TO_DATE('201702', 'YYYYMM') DT FROM DUAL)
           CONNECT BY LEVEL <= 99
          )
          SELECT B.DT, A.YMD_FR
                   , A.YMD_TO
                   , A.TTL_EVENT1
                   , A.TTL_EVENT2
                   , A.TTL_EVENT3
           FROM DATA_T A , CALENDAR_T B
WHERE B.DT BETWEEN A.YMD_FR AND A.YMD_TO
          ORDER BY B.DT--A.ID,

 

휴가자의 FROM~TO가 중간 값 없이 2개의 필드를 구하기 위해 마농님께서 쿼리 팁을 주셨고 이용해서 로우별 일자를 얻을 수 있었습니다.

저는 그래서 이 쿼리를 작성하여 달력마스터의 PK값과 작성한 DT의 값을 조인하여 아웃룩 일정관리와 같이 일자별 휴가현황을 달력에 표시하고 했습니다.

위는 쿼리문이고 실제 실행하면 아래와 같은 데이터가 조회 되는것 까지 좋은데 여기서 문제가

DT가 중복이 나기 때문에 실제 달력테이블과 아우터 조인하면 2번째 로우부터는 값이 안나와서 문제가 되는거고

DT YMD_FR YMD_TO TTL_EVENT1 TTL_EVNT2 TTL_EVNT2
20170201 20170201 20170203 N개 이름1 N개 이름2 N개 이름3
20170201 20170201 20170202 N개 이름1 N개 이름2 N개 이름3
20170201 20170201 20170202 N개 이름1 N개 이름2 N개 이름3
20170202 20170202 20170202 N개 이름1 N개 이름2 N개 이름3
20170202 20170202 20170202 N개 이름1 N개 이름2 N개 이름3
20170203 20170203 0170203 N개 이름1 N개 이름2 N개 이름3
20170203 20170203 0170204 N개 이름1 N개 이름2 N개 이름3
20170203 20170203 0170205 N개 이름1 N개 이름2 N개 이름3
20170203 20170203 0170206 N개 이름1 N개 이름2 N개 이름3

 

처리가 되었으면 하는 방식은 DT가 PK 값이 되면 좋겠고

TTL_EVENT1, TTL_EVENT2, TTL_EVENT3에 YMD_FR~YMD_TO에 속한다면 이름이 쭉 TTL_EVENT1..TTL_EVENT2...TTL_EVENT3... 휴가기간동안의 날짜에 해서 계속 나왔으면 좋겠습니다

TTL_EVENT3까지는 달력에 표시하기 위한 용도 이고 디테일화면은 따로 만들기는 할 것 입니다(TTL_EVENT1~3까지는 사번이 빠른순으로 하여 보여줄 것 입니다.)

토 일 밤새며 이렇게 저렇게 해도 방법이 안나오는데 좋은 방법 및 팁 주시면 감사하겠습니다...

DT YMD_FR YMD_TO TTL_EVENT1 TTL_EVNT2 TTL_EVNT2
20170201 20170201 20170203 N개 이름1 N개 이름2 N개 이름3
20170202 20170202 20170204 N개 이름1 N개 이름2 N개 이름3
20170203 20170203 20170205 N개 이름1 N개 이름2 N개 이름3
20170204 20170204 20170206 N개 이름1 N개 이름2 N개 이름3
by 마농 [2019.08.28 08:55:07]

조회 월에 대한 일자별 휴가자 내역을 뽑는 건가요?
1. 일자별로 뽑는 건데? from ~ to 는 왜 조회하나요?
 - from ~ to 는 출력 항목에서 빼야 할 것 같네요.
2. 휴가자는 3명만 필요한가요?
 - 4명 이후로는 조회가 안되는데? 이래도 문제가 없는지?
3. 조건은 월 조건 밖에 없는 건가요?
 - 부서조건이라던가 이런거는 없는지?
4. 조인을 많이 하는데?
 - 조인이 필요한지도 의문이네요?
 - 지금 쿼리만 봐서는 d 테이블만 필요할 것 같네요.
 - m, c, s, e 없어도 d 만가지고 뽑으면 될 것 같은데요.


by 마농 [2019.08.28 09:04:55]
SELECT dt
     , MIN(DECODE(rn, 1, nm_user)) user_1
     , MIN(DECODE(rn, 2, nm_user)) user_2
     , MIN(DECODE(rn, 3, nm_user)) user_3
     , COUNT(cd_user) cnt
  FROM (SELECT a.dt
             , b.cd_user
             , fn_get_emp_nm(b.cd_user) nm_user
             , ROW_NUMBER() OVER(PARTITION BY a.dt ORDER BY b.cd_user) rn
          FROM (SELECT TO_CHAR(dt + LEVEL - 1, 'yyyymmdd') dt
                  FROM (SELECT TO_DATE('201702', 'yyyymm') dt FROM dual)
                 CONNECT BY LEVEL <= LAST_DAY(dt) - dt + 1
                ) a
          LEFT OUTER JOIN t_vac_220 b
            ON a.dt BETWEEN b.ymd_fr AND b.ymd_to
        )
 GROUP BY dt
 ORDER BY dt
;

 


by 개발고수가되기를 [2019.08.28 09:38:23]

정말 감사합니다 마농님 같은 분이 사수면 참 좋겠습니다 정말 감사드립니다.

조회 월에 대한 일자별 휴가자 내역을 뽑는 건가요? => 네, 그렇습니다 1달기준 달력으로 해당 휴가자를 일마다 표시해 주려고 했는데 이게 달력칸이 작아 3명정도 보여주려고 했는데 비효율적인것 같아 디테일 화면을 주고 해당일자의 휴가자의 카운트 정도를 보여줄까 싶습니다.


1. 일자별로 뽑는 건데? from ~ to 는 왜 조회하나요?
 - from ~ to 는 출력 항목에서 빼야 할 것 같네요.

==> 그러게요 저렇게 조인걸어서 from~to 기간을 뽑아야 했는데요..
2. 휴가자는 3명만 필요한가요?
 - 4명 이후로는 조회가 안되는데? 이래도 문제가 없는지? ==> 첫번째 답변 드린 것처럼 달력한칸한칸에 휴가자를 3명정도 표시하고 디테일에서 해당일의 휴가자를 쭉 나열해서 보여주려고 했는데 의미가 없는 것 같아 카운트만 보여줄까 합니다
3. 조건은 월 조건 밖에 없는 건가요?
 - 부서조건이라던가 이런거는 없는지?

네 D Table 말고 조인이 걸려 있는 이유가 부서, 휴가진행상태 등등 조건이 있어서 조인 되었습니다!
4. 조인을 많이 하는데?
 - 조인이 필요한지도 의문이네요?
 - 지금 쿼리만 봐서는 d 테이블만 필요할 것 같네요.
 - m, c, s, e 없어도 d 만가지고 뽑으면 될 것 같은데요.

=> 네 간결하게 최대한 조인 뺄거는 빼고 작성 하도록 하겠습니다.

 

아 진짜 너무 감사합니다 제가 되게 고민했는데 한수 배웠습니다

 

마농님 제가 일반적은 쿼리는 이제 보면 좀 아는 수준이 되었는데요

계층형쿼리라던지( () over )

connect by level

요런거 사용하는거 잘 정리된거 혹시 사이트에 있을까요 몇일전부터 시간날떄마다 검색해서 보는데 머리가 꼴통이라 이해가 안되네요..

 

정말 감사하고 중간중간에 물 많이 마시고 많이 걸으십시오!


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