CODEWITHSUNDEEP
sqloracle-database
Janie
Janie

Reputation: 3

Grab the Last Date when a value changes

I need to grab the last rundate when then churn value changed. I would need the result to return this: 

> customer  billto   banner     rundate        churn    lastchurndate

> 976193     976193   GexPro     12/04/2019     true     11/26/2019
> 976193     976193   GexPro     11/26/2019     true     11/26/2019  
> 976193     976193   GexPro     11/19/2019     false    11/26/2019

This is the current raw data set: 

> customer  billto   banner     rundate        churn    

> 976193     976193  GexPro     12/04/2019     true      

>976193     976193   GexPro     11/26/2019     true     

>976193     976193   GexPro     11/19/2019     false

The churn value changed on 11/26/2019 from False to True.

I need to know when the last date was when churn <> current churn date if I were to query on current rundate as of today.

I have a query where I was able to get 11/19/2019, but it's not quit right. 

select
    v.st_cust_no
    ,v.bt_cust_no
    ,v.banner
    ,v.rundate
    ,v.churn
    ,case when c."last churn date" is null then v.rundate
        else c."last churn date"
     end as "last churn date" 
    ,c."last churn" 
    from 
     dwstage v
    left join 
    (    
    select 
     z.st_cust_no
    ,z.BT_CUST_NO
    ,z.banner
    ,z.rundate 
    ,z.churn as "current churn" 
    ,x.latest as "last churn date" 
    ,x.churn "last churn" 

    from dwstage    z

        inner join 
        (select 
             st_cust_no
            ,BT_CUST_NO
            ,banner
            ,churn
            ,max(rundate) as latest 
            from dwstage
            group by 
                 st_cust_no
                ,BT_CUST_NO
                ,banner
                ,churn) x

        on z.st_cust_no = x.st_cust_no
        and z.bt_cust_no = x.bt_cust_no
        and z.BANNER = x.BANNER
    where z.churn <> x.churn
    ) c
     on v.st_cust_no = c.st_cust_no
        and v.bt_cust_no = c.bt_cust_no
        and v.BANNER = c.BANNER
        and v.rundate = c.rundate
-- where v.st_cust_no = '14025'
order by 
    v.st_cust_no
   ,v.banner
   ,v.rundate desc 
;

Upvotes: 0

Views: 155

Answers (3)

user5683823
user5683823

Reputation:

Oracle 12.1 introduced the match_recognize clause, which can do very quick work on this kind of problem. Here is one way. I start with creating a small test table, with two different customers to test that the query works correctly on that more general scenario.

Table creation:

create table dwstage (customer, billto,  banner, rundate, churn) as
  select 976193, 976193, 'GexPro', to_date('12/04/2019', 'mm/dd/yyyy'), 'true'  from dual union all
  select 976193, 976193, 'GexPro', to_date('11/26/2019', 'mm/dd/yyyy'), 'true'  from dual union all
  select 976193, 976193, 'GexPro', to_date('11/19/2019', 'mm/dd/yyyy'), 'false' from dual union all
  select 999999, 999999, 'Banner', to_date('03/21/2019', 'mm/dd/yyyy'), 'false' from dual union all
  select 999999, 999999, 'Banner', to_date('04/21/2019', 'mm/dd/yyyy'), 'true'  from dual union all
  select 999999, 999999, 'Banner', to_date('08/03/2019', 'mm/dd/yyyy'), 'false' from dual union all
  select 999999, 999999, 'Other' , to_date('08/31/2019', 'mm/dd/yyyy'), 'false' from dual
;

Query and output:

select customer, billto, banner, rundate, churn, lastchurndate
from   dwstage
match_recognize(
  partition by customer
  order     by rundate desc
  measures  final last(a.rundate) as lastchurndate
  all rows  per match
  pattern   (^ a+ b*)
  define    a as churn = first(churn)
)
;


  CUSTOMER     BILLTO BANNER RUNDATE    CHURN LASTCHURND
---------- ---------- ------ ---------- ----- ----------
    976193     976193 GexPro 12/04/2019 true  11/26/2019
    976193     976193 GexPro 11/26/2019 true  11/26/2019
    976193     976193 GexPro 11/19/2019 false 11/26/2019
    999999     999999 Other  08/31/2019 false 08/03/2019
    999999     999999 Banner 08/03/2019 false 08/03/2019
    999999     999999 Banner 04/21/2019 true  08/03/2019
    999999     999999 Banner 03/21/2019 false 08/03/2019

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You want the date on every row, so you do not want any filtering. This suggests window functions:

select s.*,
       max(case when prev_churn <> churn then rundate) over (partition by customer, billto) as last_churndate
from (select s.*,
             lag(churn) over (order by customer, billto order by rundate) as prev_churn
      from dwstage s
     ) s;

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can take advantage of analytical functions as following:

Select * from
  (Select t.*,
          Row_number() over (partition by customer order by rundate desc nulls last) as rn
   from
     (Select t.*, 
             case when lag(chrun) over (partition by cutomer order by rundate) <> chrun then 1 end as chrunchanged
        From your_table t) t
  Where chrunchanged = 1)
Where rn = 1

Cheers!!

Upvotes: 1

Related Questions