[오라클]sql 팁 날짜관련 주차구하기

오늘이 1년 기준으로 몇주차인지 구하려고 할때 필요한 sql

SELECT DT
        ,TO_CHAR(TRUNC(dt) + tmpCnt,'w') "주차(일)"
        ,TO_CHAR(TRUNC(dt) + tmpCnt + CASE WHEN tmpCnt = 0 THEN 6 ELSE -1 END,'w') "주차(월)"
        ,TO_CHAR(TRUNC(dt) + tmpCnt + CASE WHEN tmpCnt <= 1 THEN 5 ELSE -2 END,'w') "주차(화)"
        ,TO_CHAR(TRUNC(dt) + tmpCnt + CASE WHEN tmpCnt <= 2 THEN 4 ELSE -3 END,'w') "주차(수)"
        ,TO_CHAR(TRUNC(dt) + tmpCnt + CASE WHEN tmpCnt <= 3 THEN 3 ELSE -4 END,'w') "주차(목)"
        ,TO_CHAR(TRUNC(dt) + tmpCnt + CASE WHEN tmpCnt <= 4 THEN 2 ELSE -5 END,'w') "주차(금)"
        ,TO_CHAR(TRUNC(dt) + tmpCnt + CASE WHEN tmpCnt <= 5 THEN 1 ELSE -6 END,'w') "주차(토)"
FROM (
SELECT dt
,TRUNC(dt,'mm') - TRUNC(TRUNC(dt,'mm'),'d') tmpCnt
FROM (SELECT TRUNC(TO_DATE('20110101'),'yy') + LEVEL - 1 dt
FROM dual
CONNECT BY LEVEL <= 365)
)
;

 
  select  to_char(week_dt, 'yyyy-mm')
         ,week_y
    from (
          select  dt
                , decode(week_y, 0, floor((TRUNC(dt ,'yy')-1  - NEXT_DAY(TRUNC(TRUNC(dt ,'yy')-1 ,'yy')-1, '월요일' ))/7)+1
                               , week_y ) as week_y
                , week_m
                , decode(week_m, 0, TRUNC(dt ,'mm')-1,dt)  as week_dt
            from (
                  select  dt
                        , floor((dt - NEXT_DAY(TRUNC(dt, 'yy') -1, '월요일' ))/7)+1 as week_y
                    
                        , floor((dt - NEXT_DAY(TRUNC(dt, 'mm') -1, '월요일' ))/7)+1 week_m --월간주차
                    from (
                          SELECT TRUNC(TO_DATE('20070101'),'yy') + LEVEL - 1 dt
                            FROM dual
                          CONNECT BY LEVEL <= 365
                         ) a
                  )
           )
 
  ;

 

--일주일 (7일) 단위로 n월 1~5주차를 보여주기 (다음달 시작일자가 포함된 월 마지막주 는 다음달 1주차로 설정)

 

  select to_char(DT,'yyyymmdd') writedate
           --,TO_CHAR(TRUNC(dt) + tmpCnt + CASE WHEN tmpCnt = 0 THEN 6 ELSE -1 END,'w') as jucha_m
          -- ,TO_CHAR (TRUNC(dt) , 'IW') JUCHA_y
           ,MAX(to_char(dt,'mm')) OVER (PARTITION BY TO_CHAR (TRUNC(dt) , 'IW')) month_val
           ,min(TO_CHAR(TRUNC(dt) + tmpCnt + CASE WHEN tmpCnt = 0 THEN 6 ELSE -1 END,'w')) OVER (PARTITION BY TO_CHAR (TRUNC(dt) , 'IW')) jucha_val
       from (   
           select  dt 
                  ,TRUNC(dt,'mm') - TRUNC(TRUNC(dt,'mm'),'d') tmpCnt
                  ,lev
             from (    
                  select to_date('20140716','yyyymmdd') +level -1 dt 
                        ,level as lev
                    from dual
                  connect by LEVEL <= trunc(sysdate) - to_date('20140716','yyyymmdd')+1 
                  )
             )

 

;