등록한 휴일( 창립기념일등..) 과  주말(토,일)을 제외한    달의 마지막 4일 구하기

     WITH TB_HOLIDAY  AS (
               SELECT '20230703' AS ST_HOLIDAY_CD, '휴일1' ST_HOLIDAY_NM  FROM DUAL
               UNION SELECT '20230703' AS ST_HOLIDAY_CD, '휴일2' ST_HOLIDAY_NM  FROM DUAL
               UNION SELECT '20230713' AS ST_HOLIDAY_CD, '휴일3' ST_HOLIDAY_NM  FROM DUAL
               UNION SELECT '20230723' AS ST_HOLIDAY_CD, '휴일4' ST_HOLIDAY_NM  FROM DUAL
               UNION SELECT '20230726' AS ST_HOLIDAY_CD, '휴일5' ST_HOLIDAY_NM  FROM DUAL
               UNION SELECT '20230728' AS ST_HOLIDAY_CD, '휴일6' ST_HOLIDAY_NM  FROM DUAL
     )
     select v_date
     from  
     (
         select v_date
         from  
         (        
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') )     , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ),    'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL  
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -1 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -1, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -2 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -2, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -3 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -3, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -4 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -4, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -5 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -5, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -6 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -6, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -7 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -7, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -8 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -8, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -9 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -9, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL    
           UNION
              SELECT TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -10 , 'yyyymmdd')  v_date , DECODE ( TO_CHAR( LAST_DAY( TO_DATE( '202307', 'yyyymm') ) -10, 'D'), '1','Y','7','Y','X') isweeekd               FROM DUAL  
         ) b
         WHERE b.isweeekd = 'X'
           AND  b.v_date not in ( select st_holiday_cd from TB_HOLIDAY where st_holiday_cd   like substr( '202307', 1,6 ) || '%' )
         ORDER BY b.v_date desc
     )
     WHERE rownum <= 4