How to calculate number of renewal in PATSTAT

One piece of information that is worthy to be collected in order to have a proxy of patent life or patent value is the number of renewals (yearly fees payed) after grant.

TLS231 in PATSTAT contain the renewal information under the code PGFP.

Unfortunately such table does not contain all renewals but only last one for each country.
I paste down here a procedure that calculates the max number of renewals, as years passed from grant to last renewal, grant year and number of countries where the patent had been renewed in the last valid renewal.

Starting point is a table applnidlist with a field appln_id with the list of applications we want to enrich [table should be indexed on appln_id].


alter table applnidlist add column grantyear int(4) default null;

update applnidlist a 
  INNER JOIN patstat.tls211_pat_publn t11 ON a.appln_id = t11.APPLN_ID
SET a.grantyear=    Year(t11.PUBLN_DATE) 
WHERE
  t11.PUBLN_FIRST_GRANT = 1;
  
-- last data


drop table if exists t1;

create table t1
SELECT
  a.appln_id,
  Max(b.fee_renewal_year) AS Max_fee_renewal_year
FROM
  tazza.applnidlist a
  INNER JOIN patstat.tls231_inpadoc_legal_event b ON a.appln_id = b.appln_id
WHERE
  b.event_code = 'PGFP'
GROUP BY
  a.appln_id;
   
 alter table applnidlist add column nrenew int(4) default null;
 alter table applnidlist add column nrenewcy int(4) default null; 
 alter table applnidlist add column maxrenyear int(4) default null;
  
alter table t1 add index i1(appln_id);

update applnidlist a inner join t1 b
on a.appln_id=b.appln_id set maxrenyear=Max_fee_renewal_year;

alter table applnidlist add index i6(maxrenyear);

update
  applnidlist a
  INNER JOIN
(SELECT      b.appln_id,  b.fee_renewal_year,
Count(b.fee_country) AS Count_fee_country,
  Year(b.fee_payment_date) AS fee_payment_date
FROM   patstat.tls231_inpadoc_legal_event b
WHERE   b.event_code = 'PGFP'
GROUP BY   Year(b.fee_payment_date),   b.fee_renewal_year,   b.appln_id  ) z
   ON a.appln_id = z.appln_id AND a.maxrenyear = z.fee_renewal_year
SET
  a.nrenew = fee_payment_date - grantyear,
  a.nrenewcy=Count_fee_country;