beckham123 Ответов: 0

Назначение счетчик в зависимости от условия в Oracle

I have insert statements too below -

with inputs (REGID, Pkg_DES, PRD_DESC, EVENT_DATE, event_type_cd) as      (select 1 , CC , trail , to_date('12/12/2016', 'mm/dd/yyyy') , new sub from dual union all 
select 1 , CC , trail ,to_date('12/13/2016', 'mm/dd/yyyy') , exp from dual union all

select 1 , CC , PAID , to_date('12/14/2016', 'mm/dd/yyyy') , upsell from dual union all 
select 1 , CC , PAID , to_date('12/15/2016', 'mm/dd/yyyy'), exp from dual union all 
select 1 , CC , PAID , to_date('12/16/2016', 'mm/dd/yyyy') , renewal from dual union all 
select 1 , CC , PAID , to_date('12/17/2016', 'mm/dd/yyyy') , renewal from dual union all
select 1 , aa , trail , to_date('12/12/2016', 'mm/dd/yyyy') , new sub from dual union all 
select 1 , aa , trail , to_date('12/13/2016', 'mm/dd/yyyy') , exp from dual union all

select 1 , aa , PAID , to_date('12/14/2016', 'mm/dd/yyyy') , renewal from dual union all 
select 1 , aa , PAID , to_date('12/15/2016', 'mm/dd/yyyy') , renewal from dual union all 
select 1 , aa , PAID , to_date('12/16/2016', 'mm/dd/yyyy') , upsell from dual union all 
select 1 , aa , PAID , to_date('12/17/2016', 'mm/dd/yyyy') , renewal from dual



I have the requirement like this below

REG_ID , PACKAGE_DESC , PRODUCT_TYPE_DESC , EVENT_DATE , event_type_cd
1 , CC , trail , 12-12-2012 , new sub
1 , CC , trail , 12-13-2012 , exp
1 , CC , PAID , 12-14-2012 , upsell
1 , CC , PAID , 12-15-2012 , exp
1 , CC , PAID , 12-16-2012 , renewal
1 , CC , PAID , 12-17-2012 , renewal
1 , aa , trail , 12-12-2012 , new sub
1 , aa , trail , 12-13-2012 , exp
1 , aa , PAID , 12-14-2012 , renewal
1 , aa , PAID , 12-15-2012 , renewal
1 , aa , PAID , 12-16-2012 , upsell
1 , aa , PAID , 12-17-2012 , renewal

The OutPut i need is like below

REG_ID,Pkg_DES,PRD_DESC,EVENT_DATE,event_type_cd,renewal_cnt,is_ren, is_conv
1 , CC , trail , 12-12-2012 , new sub , 0 , 0 ,0
1 , CC , trail , 12-13-2012 , exp , 0 , 0 ,0
1 , CC , PAID , 12-14-2012 , upsell , 0 , 0 , 1
1 , CC , PAID , 12-15-2012 , exp , 0 , 0 , 1
1 , CC , PAID , 12-16-2012 , renewal , 1 , 1 , 0
1 , CC , PAID , 12-17-2012 , renewal , 2 , 1 , 0
1 , aa , trail , 12-12-2012 , new sub , 0 , 0 , 0
1 , aa , trail , 12-13-2012 , exp , 0 , 0 , 0
1 , aa , PAID , 12-14-2012 , renewal , 0 , 0 , 1
1 , aa , PAID , 12-15-2012 , renewal , 1 , 1 , 0
1 , aa , PAID , 12-16-2012 , upsell , 2 , 1 , 0
1 , aa , PAID , 12-17-2012 , renewal , 3 , 1 , 0

Conversion Logic :- If a PRD_DESC is changed from Trail to Paid for the 1st time, then it is called CONVERSION product  

Renewal Logic :- If a Paid Product is renewed from Paid to Paid, then it is called RENEWAL product  

renewal_cnt should start only at the PAID to PAID product. If the PAID to PAID is having event_type_cd as exp then the counter shouldn't increment. If the trail to paid is having event_type_cd as renewal then also it should be zero only. the event's are ordered by event_date   

is_conv should be set to 1 for the 1st Trail to Paid product.  

is ren should be set to 1 for the paid to paid product.  

Does someone have the idea how to achieve this?


Что я уже пробовал:

Я попытался использовать функцию Rank и вывести счетчик обновления, но это не сработало. :(

0 Ответов