How to track granted WIPO / PCT patents in patstat

As WIPO websites says under FAQ page,

A patent is granted by a national patent office or by a regional office that does the work for a number of countries, such as the European Patent Office and the African Regional Intellectual Property Organization. Under such regional systems, an applicant requests protection for the invention in one or more countries, and each country decides as to whether to offer patent protection within its borders. The WIPO-administered Patent Cooperation Treaty (PCT) provides for the filing of a single international patent application which has the same effect as national applications filed in the designated countries. An applicant seeking protection may file one application and request protection in as many signatory states as needed.

(another intersting page describing in detail patentin process is https://www.heerlaw.com/patent-faq)

So, the usual selection on publication first grant flag that allows to understand if a patent had been granted or not (see below) does not work with Wipo

SELECT * FROM patstat.tls211_pat_publn t
WHERE t.`PUBLN_FIRST_GRANT` = 1 AND t.`PUBLN_AUTH` = "WO";

Above query would return nothing.

So we need to get the corresponding national/regional patent and see if any of them has been granted, via INTERNAT_APPLN_ID field in table TLS201

Let me explain it via SQL: if we have a table t1 containing a list of application id, to build a table t2 containing those who are granted...


drop table if exists t2;

alter table t1 add index idx1 (appln_id);

-- temp index on TLS201 to speedup join

alter table patstat.tls201_appln add index idxtemp (INTERNAT_APPLN_ID);

-- table t2 with list of granted appln_id

create table t2
Select
  distinct t1.appln_id
From
  t1 Inner Join
  patstat.tls201_appln t2 On t1.appln_id = t2.INTERNAT_APPLN_ID Inner Join
  patstat.tls211_pat_publn t11 On t2.APPLN_ID = t11.APPLN_ID
Where
  t11.PUBLN_FIRST_GRANT = 1;

-- drop temp index on TLS201

alter table patstat.tls201_appln drop index idxtemp;

So for example if we look @ WO0149922 in espacenet, (a wonderful laundry bag application) we will find among it's equivalents patent EA003688 that has been granted and so we will find it in table T2.