patenting in the south
TRANSCRIPT
Patenting in the South available data and coverage in
PATSTAT
CRIOS
Center for Research on Innovation, Organization and Strategy
1• Gianluca Tarasconi, Crios DBA rawpatentdata.blogspot.com
Utrecht 6/5/2014
In brief
• The goal of this presentation is to
analyze EPO PATSTAT as data source
for BRICS countries and its advantages
and disadvantages.
2
Why PATSTAT
Obviously, where available the best data sources are national patent offices but some issues arise when trying to use the files to get one integrated DB (different formats, information missing, data unavailable for some authorities) and most of all the BI effort is very heavy.
EPO Patstat offers an integrated source of data, a lot of plug and play extensions (ie nutsificatons, standard names…); most of all data can be compared to other patent offices or get some other data (ie triadics/PCT/characteristic of cited / citing)
WIPO IP Statistics Data Center (on-line service enabling access to WIPO's statistical data) ,and WIPO Statistical Country Profiles for around 190 countries have a wider coverage but dont allow to build our own indicators.
On the other hand data available have different reliability and coverage: limitations apply…
5
From national patent offices
(examples): raw data• CN: http://english.sipo.gov.cn/service/:
sipo xml txt and tiff data
• IN: (in PDF)
http://ipindia.nic.in/ipr/patent/journal_archieve/
journal_2014/patent_journal_2014.htm data
• WIPO statistics (for coverage)
• http://www.wipo.int/ipstats/en/resources/
office_stats_reports.html
4
Coverage check: a sheer
publication count• Select a.APPLN_AUTH, a.FILING_YEAR, Count(a.APPLN_ID) From applications a Group By a.APPLN_AUTH,
a.FILING_YEAR
5
LAST TEN YEARS
year 'BR' 'CN' 'IN' 'RU' 'ZA'
2000 20701 111827 517 26433 3414
2001 20594 132468 443 30833 6033
2002 19221 164926 290 34122 6541
2003 20860 205108 469 38283 6365
2004 22751 235436 427 39523 7938
2005 23922 288504 450 42222 7094
2006 24318 343009 499 47683 7162
2007 18994 386766 618 49336 6994
2008 10072 464611 839 50437 5421
2009 8993 576915 748 45686 3409
2010 7646 714276 842 42720 502
(caveat: data transmission lag to EPO can differ from office to office so
comparisons amons very recent data can lead to misguiding results)
Percentage of granted patents
SQL CODE IN APPENDIX
6
% of granted patents (IN = 0%!!)
The Indian mutiny…
• Official data for India applications vs all applcount (including unpublished)
• On EPO website at page: https://data.epo.org/data/data.html coverageby authority is listed (available in absolutenumbers not in % )
IN
2007 2215
2008 2541
2009 2514
2010 2988
2011 872
2012 28
All over coverage in 2009
Reasons are: different agreements (ie India) – only
EU/PCT relevant documents integrated (ie priorities /
citations)
8
office type off data patstat %
BR 25951 9054 34.9%
IN 34287 2514 7.3%
RU A 40966 34763 84.9%
U 11671 10419 89.3%
ZA 9271 3767 40.6%
CN A+U 693917 580668 83.7%
PCT percentage
• % of PCT (note IN after Y2000 care some type A were deleted
because unpublished)
9
Percentage of tradics
• This type of counts requires integrated data from
several patent offices; code in the appendix
10
Inventors countries coverage
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
BR 1.2% 1.0% 1.2% 1.2% 1.2% 1.1% 1.1% 1.8% 5.0% 6.2%
CN 98.4% 97.6% 95.7% 91.9% 85.0% 76.7% 69.5% 66.1% 51.7% 9.6%
IN 25.7% 41.9% 58.3% 78.7% 97.1% 98.6% 99.2% 99.4% 99.3% 98.4%
RU 20.5% 27.6% 69.0% 98.4% 100.0% 99.9% 100.0% 100.0% 100.0% 100.0%
ZA 4.5% 1.8% 2.0% 2.0% 2.4% 4.2% 1.2% 1.0% 1.2% 1.5%
11
CN data decay
after 2005; RU
after 2003
nearly 100%
Applicants countries coverage
12
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
BR 99.7% 99.9% 99.9% 99.9% 99.8% 99.9% 99.9% 99.9% 100.0% 99.9%
CN 99.2% 98.9% 97.8% 95.5% 91.0% 85.9% 79.4% 71.7% 57.3% 12.3%
IN 60.6% 80.6% 94.3% 99.9% 99.4% 99.2% 99.3% 99.4% 99.1% 98.4%
RU 13.7% 16.3% 50.5% 98.8% 100.0% 100.0% 100.0% 100.0% 100.0% 99.9%
ZA 13.5% 6.0% 5.8% 6.9% 7.8% 10.0% 5.2% 2.8% 4.5% 5.7%
CN – same
decay after
2006; RU, BR
good coverage
Domestic inventions with
foreign ownership (2005)
13
pat off for ownership
BR 1.44%
CN 0.12%
IN 1.30%
RU 1.08%
ZA 2.21%
Domestic ownership of
inventions made abroad (2005)
14
pat off dom own
BR 1.73%
CN 0.09%
IN 0.60%
RU 1.09%
ZA 0.37%
Compare to EPO USPTO PCT
data (I)
CY EPO USPTO PCT
BR 29.8% 41.8% 9.3%
CN 27.9% 45.9% 7.3%
IN 31.6% 64.7% 13.4%
RU 52.4% 70.0% 12.7%
ZA 27.4% 13.3% 23.0%
15
Share of domestic inventions with foreign ownership in 2005
Values changes dramaticaly:
overlapping of different
effects (ie applied in the
name of local branch)
Compare to EPO USPTO PCT
data (II)
16
Share of domestic ownership of inventions made abroad (2005)
CY EPO USPTO PCT
BR 3.5% 1.8% 4.5%
CN 9.4% 13.1% 4.0%
IN 3.9% 3.3% 8.1%
RU 2.7% 11.6% 5.3%
ZA 3.6% 3.1% 1.0%
Less dramatic changes: only
for CN 2 orders of
magnitude of change
Average Backward Citations rate
17
2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
CN 0.0232 0.0408 0.0248 0.026 0.0327 0.0395 0.0551 0.0725 0.0711 0.0715
IN 1.1721 2.5011 4.0069 4.6503 4.6838 4.7022 4.8677 5.0243 4.9058 6.3128
RU 0.0978 0.0898 0.0778 0.0703 0.0644 0.0771 0.0778 0.084 0.0896 0.099
ZA 0.3453 0.1548 0.1536 0.156 0.1041 0.099 0.0977 0.0736 0.1096 0.1555
BR 0.0317 0.0389 0.0454 0.0448 0.0542 0.0484 0.0577 0.0944 0.1725 0.2694
IT 0.2104 0.2606 0.3356 0.3478 0.3351 0.3589 0.3345 0.3708 1.8654 3.4196
AVG 0.3134 0.5143 0.774 0.8825 0.879 0.8875 0.915 0.9532 1.2023 1.7213
Italy has been added as benchmark; for all countries (apart India
with mainly PCT only apps) most citations are not included
Average Forward Citations rate
18
Italy has been added as benchmark; for all countries (apart India with
mainly PCT only apps) many citations are not included since originated
from national patents2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
'CN' 0.1087 0.1136 0.1177 0.1165 0.1073 0.0974 0.0898 0.0823 0.0648 0.0436
'IN' 0.9168 1.6411 2.3759 2.6972 2.7822 2.4778 1.992 1.5534 1.0644 0.5829
'RU' 0.1377 0.1119 0.0841 0.0796 0.0636 0.0479 0.0365 0.0289 0.0196 0.013
'ZA' 0.1415 0.0736 0.0624 0.0855 0.0325 0.0302 0.0243 0.0104 0.0153 0.0079
'BR' 0.0153 0.0125 0.0188 0.0168 0.0207 0.0166 0.0173 0.0203 0.0262 0.0213
'IT' 0.1249 0.1342 0.1489 0.139 0.116 0.1131 0.0925 0.0782 0.063 0.0242
0.2408 0.3478 0.4679 0.5224 0.5203 0.4638 0.3754 0.2955 0.2088 0.1154
Forward
citations
contained are
more alike
other countries
patterns: focus
on them
Who cites BRICS (patent
authorities 2000-2009)BR BR 20.7%
BR US 20.7%
BR EP 20.1%
BR GB 5.9%
BR DE 4.2%
BR OTH 28.4%
CN CN 60.4%
CN US 24.7%
CN EP 4.7%
CN GB 2.2%
CN WO 1.4%
CN OTH 6.6%
IN US 23.6%
IN EP 19.8%
IN IN 13.7%
IN WO 9.2%
IN GB 5.5%
IN OTH 28.2%
RU RU 36.1%
RU US 23.3%
RU EA 9.8%
RU EP 7.9%
RU DE 3.2%
RU OTH 19.7%
ZA US 25.1%
ZA EP 17.9%
ZA GB 8.9%
ZA WO 8.4%
ZA AU 7.1%
ZA OTH 32.7%
19
Yellow slice = domestic
citations; CN/RU very
high; ZA not in top 5
Who cites BRICS: focus on CN
domestic citation rate by year
pat off % year
'CN' 45.1% 2000
'CN' 45.5% 2001
'CN' 48.3% 2002
'CN' 49.0% 2003
'CN' 53.5% 2004
'CN' 58.0% 2005
'CN' 64.2% 2006
'CN' 67.8% 2007
'CN' 70.9% 2008
'CN' 77.7% 2009
20
Conclusions
• Patstat is a good source of data but
cannot be taken ‘as it is’ (validation of
data must be done);
• Patent offices important for EU have
better coverage compared to non
strategic;
• Improvements will take place over time.
21
Appendix 1: SQL code for
PATSTAT (I)• create table applications
• Select
• APPLN_ID,
• APPLN_AUTH,
• APPLN_NR,
• APPLN_KIND,
• year(appln_filing_date) As FILING_YEAR,
• APPLN_AUTH As REFAPPLN_AUTH,
• "N" As TRIADIC_FLAG,
• "N" As GRANTED,
• IF(appln_kind = "W", "Y", "N") as PCT
• From
• patstat.tls201_appln T01
• where
• appln_auth in ("CN","BR", "RU", "IN", "CN", "ZA") and appln_kind <> "D2"
• and year(appln_filing_date) <>9999;
• -- Removes unpublished
• delete a.*
• From
• applications a Left Join
• patstat.tls211_pat_publn b On a.APPLN_ID = b.APPLN_ID
• where b.appln_id is null;
22
Appendix 1: SQL code for
PATSTAT (II)• -- sets grant flag
• update
• applications a Inner Join
• patstat.tls211_pat_publn b On a.APPLN_ID = b.APPLN_ID
• set
• a.GRANTED = "Y"
• Where
• b.PUBLN_FIRST_GRANT = 1;
• -- sets triadic flag
• update
• patstat.triadic_inpadoc t1 Inner Join
• patstat.tls219_inpadoc_fam t19 On t1.INPADOC_FAMILY_ID = t19.INPADOC_FAMILY_ID
• Inner Join
• applications t2 On t19.APPLN_ID = t2.APPLN_ID
• set
• t2.TRIADIC_FLAG = "Y";
23
Appendix 1: SQL code for
PATSTAT (III)• -- TRIADIC INPADOC CALCULATION
• -- EP + JP + US grants based on inpadoc family
• -- inpadoc triadic families
• drop table if exists test.t1;
• drop table if exists test.t2;
• drop table if exists test.t3;
• drop table if exists test.t4;
• -- T4: US EP JP publications excluding D2
• create table test.t4
• Select distinct
• t1.APPLN_ID,
• t11.PUBLN_AUTH,
• t11.publn_first_grant
• From
• patstat.tls201_appln t1 Inner Join
• patstat.tls211_pat_publn t11
• On t1.APPLN_ID = t11.APPLN_ID
• Where
• (t11.PUBLN_AUTH = "JP" or t11.PUBLN_AUTH = "US" or t11.PUBLN_AUTH = "EP")
• and t1.APPLN_KIND <> "D2";
24
Appendix 1: SQL code for
PATSTAT (IV)• ALTER TABLE `test`.`t4` ADD INDEX `Index_1`(`appln_ID`), ADD INDEX `Index_2`(`PUBLN_AUTH`);
• -- EP
• create table test.t1
• Select distinct
• t19.INPADOC_FAMILY_ID
• From
• patstat.tls219_inpadoc_fam t19 Inner Join
• test.t4 t11 On t11.APPLN_ID = t19.APPLN_ID
• -- patstat.tls211_pat_publn t11 On t11.APPLN_ID = t19.APPLN_ID
• where
• t11.PUBLN_AUTH= "EP";
• -- Us
• create table test.t2
• Select distinct
• t19.INPADOC_FAMILY_ID
• From
• patstat.tls219_inpadoc_fam t19 Inner Join
• test.t4 t11 On t11.APPLN_ID = t19.APPLN_ID
• -- patstat.tls211_pat_publn t11 On t11.APPLN_ID = t19.APPLN_ID
• where
• t11.PUBLN_AUTH= "US“ and t11.publn_first_grant = 1;
25
Appendix 1: SQL code for
PATSTAT (V)• -- JP
• create table test.t3
• Select distinct
• t19.INPADOC_FAMILY_ID
• From
• patstat.tls219_inpadoc_fam t19 Inner Join
• test.t4 t11 On t11.APPLN_ID = t19.APPLN_ID
• -- patstat.tls211_pat_publn t11 On t11.APPLN_ID = t19.APPLN_ID
• where
• t11.PUBLN_AUTH= "JP";
• -- triadic_inpadoc
• ALTER TABLE `test`.`t1` ADD INDEX `Index_1`(`INPADOC_FAMILY_ID`);
• ALTER TABLE `test`.`t2` ADD INDEX `Index_1`(`INPADOC_FAMILY_ID`);
• ALTER TABLE `test`.`t3` ADD INDEX `Index_1`(`INPADOC_FAMILY_ID`);
• drop table if exists patstat.triadic_inpadoc;
• create table patstat.triadic_inpadoc
• Select distinct
• t1.INPADOC_FAMILY_ID
• From
• test.t1 t1 Inner Join
• test.t2 t2 On t1.INPADOC_FAMILY_ID = t2.INPADOC_FAMILY_ID Inner Join
• test.t3 t3 On t2.INPADOC_FAMILY_ID = t3.INPADOC_FAMILY_ID;
• ALTER TABLE `patstat`.`triadic_inpadoc` ADD PRIMARY KEY (`INPADOC_FAMILY_ID`);26
Appendix 1: SQL code for
PATSTAT (VI)• -- PCT / TRIADIC / GRANTED CALC
• Select a.FILING_YEAR, a.REFAPPLN_AUTH, Sum(If(a.GRANTED = 'Y', 1, 0)) / Count(a.APPLN_ID) As gr,
• Sum(If(a.TRIADIC_FLAG = 'Y', 1, 0)) / Count(a.APPLN_ID) As tr, Sum(If(a.PCT = 'Y', 1, 0)) / Count(a.APPLN_ID) As pct
• From applications a Group By a.FILING_YEAR, a.REFAPPLN_AUTH
• -- count of country coverage by applicant
• -- note do not count distincts but by applications
• Select
• Count(t1.APPLN_ID), Year(t1.APPLN_FILING_DATE) As yy, t1.APPLN_AUTH,
• Sum(If(t6.PERSON_CTRY_CODE = '', 0, 1)) / Count(t6.person_id) As appcyperc
• From
• patstat.tls201_appln t1 Inner Join
• patstat.tls207_pers_appln t7 On t1.APPLN_ID = t7.APPLN_ID Inner Join
• patstat.tls206_person t6 On t7.PERSON_ID = t6.PERSON_ID
• Where
• Year(t1.APPLN_FILING_DATE) >= 2000 And
• Year(t1.APPLN_FILING_DATE) <= 2009 And
• t7.APPLT_SEQ_NR > 0 And (t1.APPLN_AUTH = ‘BR’ or t1.APPLN_AUTH = 'CN' Or t1.APPLN_AUTH = 'IN' Or
t1.APPLN_AUTH = 'RU' Or t1.APPLN_AUTH = 'ZA')
• Group By
• Year(t1.APPLN_FILING_DATE), t1.APPLN_AUTH
27
Appendix 1: SQL code for
PATSTAT (VII)• -- count of country coverage by inventor
• -- note do not count distincts but by applications
• Select
• Count(t1.APPLN_ID), Year(t1.APPLN_FILING_DATE) As yy, t1.APPLN_AUTH,
• Sum(If(t6.PERSON_CTRY_CODE = '', 0, 1)) / Count(t6.person_id) As appcyperc
• From
• patstat.tls201_appln t1 Inner Join patstat.tls207_pers_appln t7 On t1.APPLN_ID = t7.APPLN_ID Inner Join
• patstat.tls206_person t6 On t7.PERSON_ID = t6.PERSON_ID
• Where
• Year(t1.APPLN_FILING_DATE) >= 2000 And Year(t1.APPLN_FILING_DATE) <= 2009 And
• t7.INVT_SEQ_NR > 0 And And (t1.APPLN_AUTH = ‘BR’ or t1.APPLN_AUTH = 'CN' Or t1.APPLN_AUTH = 'IN' Or
t1.APPLN_AUTH = 'RU' Or t1.APPLN_AUTH = 'ZA')
• Group By Year(t1.APPLN_FILING_DATE), t1.APPLN_AUTH, t7
28
Appendix 1: SQL code for
PATSTAT (VIII)• -- Share of domestic inventions with foreign ownership in 2009
• Select
• a.APPLN_AUTH,
• sum(if(t6a.PERSON_CTRY_CODE <> t6i.PERSON_CTRY_CODE,1,0))/ count(t6a.PERSON_CTRY_CODE)
• From
• patstat.tls201_appln a Inner Join
• patstat.tls207_pers_appln t7a On a.APPLN_ID = t7a.APPLN_ID Inner Join
• patstat.tls207_pers_appln t7i On a.APPLN_ID = t7i.APPLN_ID Inner Join
• patstat.tls206_person t6i On t7i.PERSON_ID = t6i.PERSON_ID Inner Join
• patstat.tls206_person t6a On t7a.PERSON_ID = t6a.PERSON_ID
• Where
• (Year(a.APPLN_FILING_DATE) = 2005) and
• (a.APPLN_AUTH = 'IN' Or a.APPLN_AUTH = 'CN' Or a.APPLN_AUTH = 'BR' Or a.APPLN_AUTH = 'RU' Or a.APPLN_AUTH =
'ZA')
• And t7i.INVT_SEQ_NR > 0 And t7a.APPLT_SEQ_NR > 0
• and a.APPLN_AUTH=t6i.PERSON_CTRY_CODE and t6a.PERSON_CTRY_CODE <>””
• group by a.APPLN_AUTH
29
Appendix 1: SQL code for
PATSTAT (IX)• -- Share of domestic ownership of inventions made abroad in 2009
• Select
• a.APPLN_AUTH,
• sum(if(t6i.PERSON_CTRY_CODE <> t6a.PERSON_CTRY_CODE,1,0))/ count(t6i.PERSON_CTRY_CODE)
• From
• patstat.tls201_appln a Inner Join
• patstat.tls207_pers_appln t7a On a.APPLN_ID = t7a.APPLN_ID Inner Join
• patstat.tls207_pers_appln t7i On a.APPLN_ID = t7i.APPLN_ID Inner Join
• patstat.tls206_person t6i On t7i.PERSON_ID = t6i.PERSON_ID Inner Join
• patstat.tls206_person t6a On t7a.PERSON_ID = t6a.PERSON_ID
• Where
• (Year(a.APPLN_FILING_DATE) = 2005) and
• (a.APPLN_AUTH = 'IN' Or a.APPLN_AUTH = 'CN' Or a.APPLN_AUTH = 'BR' Or a.APPLN_AUTH = 'RU' Or a.APPLN_AUTH =
'ZA')
• And t7i.INVT_SEQ_NR > 0 And t7a.APPLT_SEQ_NR > 0
• and a.APPLN_AUTH=t6a.PERSON_CTRY_CODE and t6i.PERSON_CTRY_CODE <>"“
• group by a.APPLN_AUTH
30
Appendix 1: SQL code for
PATSTAT (X)• -- count of backward citations rate
• select APPLN_AUTH, yy, sum(ncit)/ count(appln_id) as avgcit from
• (Select t1.APPLN_AUTH, year(t1.APPLN_FILING_DATE) as yy, t1.appln_id,
• ifnull(count(t12.CITED_PAT_PUBLN_ID), 0) as ncit
• From
• patstat.tls201_appln t1 Inner Join patstat.tls211_pat_publn t11 On t11.APPLN_ID = t1.APPLN_ID
• left Join patstat.tls212_citation t12 On t11.PAT_PUBLN_ID = t12.PAT_PUBLN_ID
• Where
• Year(t1.APPLN_FILING_DATE) >= 2000 And Year(t1.APPLN_FILING_DATE) <= 2009 And
• (t1.APPLN_AUTH = 'CN' Or t1.APPLN_AUTH = 'IN' Or t1.APPLN_AUTH = 'RU' Or t1.APPLN_AUTH = 'ZA‘Or
t1.APPLN_AUTH = ‘BR’) group by t1.APPLN_AUTH, t1.appln_id, year(t1.APPLN_FILING_DATE)) as AA
• group by APPLN_AUTH,yy;
• -- count of forward citations rate
• select APPLN_AUTH, yy, sum(ncit)/ count(appln_id) as avgcit from
• (Select t1.APPLN_AUTH, year(t1.APPLN_FILING_DATE) as yy, t1.appln_id,
• ifnull(count(t12.CITED_PAT_PUBLN_ID), 0) as ncit
• From
• patstat.tls201_appln t1 Inner Join patstat.tls211_pat_publn t11 On t11.APPLN_ID = t1.APPLN_ID
• left Join patstat.tls212_citation t12 On t11.PAT_PUBLN_ID = t12.CITED_PAT_PUBLN_ID
• Where
• Year(t1.APPLN_FILING_DATE) >= 2000 And Year(t1.APPLN_FILING_DATE) <= 2009 And
• (t1.APPLN_AUTH = 'CN' Or t1.APPLN_AUTH = 'IN' Or t1.APPLN_AUTH = 'RU' Or t1.APPLN_AUTH = 'ZA‘Or
t1.APPLN_AUTH = ‘BR’ group by t1.APPLN_AUTH, t1.appln_id, year(t1.APPLN_FILING_DATE)) as AA
• group by APPLN_AUTH,yy; 31
Appendix 1: SQL code for
PATSTAT (XI)• -- raw count of citations received by appln auth
• Select
• t1.APPLN_AUTH, Count(Distinct t12.PAT_PUBLN_ID)/Count(Distinct t11.PAT_PUBLN_ID) As perc_rec,
• t1b.APPLN_AUTH As `citing_ APPLN_AUTH1`, Year(t1.APPLN_FILING_DATE) As yy
• From
• patstat.tls201_appln t1 Inner Join patstat.tls211_pat_publn t11 On t1.APPLN_ID = t11.APPLN_ID Inner Join
• patstat.tls212_citation t12 On t11.PAT_PUBLN_ID = t12.CITED_PAT_PUBLN_ID Inner Join
• patstat.tls211_pat_publn t11b On t12.PAT_PUBLN_ID = t11b.PAT_PUBLN_ID Inner Join
• patstat.tls201_appln t1b On t11b.APPLN_ID = t1b.APPLN_ID
• Where
• (Year(t1.APPLN_FILING_DATE) < 2010 And Year(t1.APPLN_FILING_DATE) > 1999) And
• (t1.APPLN_AUTH = 'CN' Or t1.APPLN_AUTH = 'IN' Or t1.APPLN_AUTH = 'RU' Or t1.APPLN_AUTH = 'ZA‘’ or t1.APPLN_AUTH = ‘BR’)
• Group By t1.APPLN_AUTH, t1b.APPLN_AUTH, Year(t1.APPLN_FILING_DATE)
• -- very raw count by inv citing country (full count so sum of ratio > 1)
• Select
• t1.APPLN_AUTH, t6.PERSON_CTRY_CODE As citing_inv_cy, Year(t1.APPLN_FILING_DATE) As yy,
• Count(Distinct t11.PAT_PUBLN_ID)/Count(Distinct t12.PAT_PUBLN_ID) as cyratio
• From
• patstat.tls201_appln t1 Inner Join patstat.tls211_pat_publn t11 On t1.APPLN_ID = t11.APPLN_ID Inner Join
• patstat.tls212_citation t12 On t11.PAT_PUBLN_ID = t12.CITED_PAT_PUBLN_ID Inner Join
• patstat.tls211_pat_publn t11b On t12.PAT_PUBLN_ID = t11b.PAT_PUBLN_ID Inner Join
• patstat.tls207_pers_appln t7 On t11b.APPLN_ID = t7.APPLN_ID Inner Join patstat.tls206_person t6 On t7.PERSON_ID = t6.PERSON_ID
• Where
• (Year(t1.APPLN_FILING_DATE) < 2010 And Year(t1.APPLN_FILING_DATE) > 1999) And
• t7.INVT_SEQ_NR > 0 and (t1.APPLN_AUTH = 'CN' Or t1.APPLN_AUTH = 'IN' Or t1.APPLN_AUTH = 'RU'
• Or t1.APPLN_AUTH = 'ZA' Or t1.APPLN_AUTH = 'BR')
• Group By t1.APPLN_AUTH, Year(t1.APPLN_FILING_DATE), t7.INVT_SEQ_NR, t6.PERSON_CTRY_CODE32