구간별 집계하기

2011-07-28 19:29:39

---

 

SQL 4 Oracle 오라클용 sql 실무

▒ 동일인 취득형태별 최초취득일자 구간별 점수

 

select 고객ID, 고객명, 취득형태 ,  sum(점수) 구간별취득점수,
        (case  

              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <=  3)  

              then '03개월이하'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <=  6)

              then '06개월이하'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <= 12)

              then '12개월초과'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <= 24)

              then '24개월이하'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <= 36)

              then '36개월이하'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) >  36)

              then '36개월초과'
              else 'x' end) 동일인_최초취득일기준구간,

from (
       select 고객ID, 고객명, 점수, 취득형태, 취득일자,
            min( nvl(취득일자,to_char(sysdate,'yyyymmdd') )  ) OVER(PARTITION BY 고객명,취득형태 ) 최초취득일자
        from   목적_Table명
        where 고객상태 = '활동중'

) group by 고객ID, 고객명,취득형태,
        (case  

              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <=  3)  

              then '03개월이하'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <=  6)

              then '06개월이하'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <= 12)

              then '12개월초과'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <= 24)

              then '24개월이하'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) <= 36)

              then '36개월이하'
              when (trunc( months_between(last_day(sysdate),last_day(substr(최초취득일자,1,6)||'01' ) )) >  36)

              then '36개월초과'

              else 'x' end)