Number of patents per Country, per IPC code, per Year

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

micvollaro
Posts: 3
Joined: Tue Oct 15, 2019 3:59 pm

Number of patents per Country, per IPC code, per Year

Post by micvollaro » Tue Oct 15, 2019 4:15 pm

Dear staff, I need to know the lines to get the following information:
1.
The number of USA patents belonging to the A01 and C05 classes (separately), registered in EU countries (AT, BE, DK, FI, FR,DE, EL, EI, IT, NL, NO, PT, ES, SE, CH, UK), yearly from 1970 to 2018 (develped in the US and registered in EU countries).

2.
The number of domestic patents belonging to the A01 and C05 classes (separately), registered in EU countries (AT, BE, DK, FI, FR,DE, EL, EI, IT, NL, NO, PT, ES, SE, CH, UK), yearly from 1970 to 2018 (developed and registered in the same country).

I tried to write some coding without success.

Thanks for your help!


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Number of patents per Country, per IPC code, per Year

Post by EPO / PATSTAT Support » Wed Oct 16, 2019 10:49 am

Hello Micvollaro,
For question 1: how do you define "US patents"? Are you thinking about the geographical coverage reflected in the patent family, or the fact that "the applicant" is US based. (Or any other definition ?)
Similar to question 2: what is your definition of a domestic patent ?
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


micvollaro
Posts: 3
Joined: Tue Oct 15, 2019 3:59 pm

Re: Number of patents per Country, per IPC code, per Year

Post by micvollaro » Wed Oct 16, 2019 1:46 pm

Dear staff, for US patent I intend that the applicant is US based, either a firm or institution (private and public); the same I intend for domestic.

Hope that this clarifies. Thanks.


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Number of patents per Country, per IPC code, per Year

Post by EPO / PATSTAT Support » Thu Oct 17, 2019 1:28 pm

Here is a sample query that gives you the number of applications filed in your specified list of countries for applicants that have "US" as country code. You can further extend it to your needs.

Code: Select all

SELECT psn_name, left(ipc_class_symbol,3) IPC,
COUNT(distinct(case when year(appln_filing_date) = 2000 then tls201_appln.appln_id end)) as "2000",
COUNT(distinct(case when year(appln_filing_date) = 2001 then tls201_appln.appln_id end)) as "2001",
COUNT(distinct(case when year(appln_filing_date) = 2002 then tls201_appln.appln_id end)) as "2002",
COUNT(distinct(case when year(appln_filing_date) = 2003 then tls201_appln.appln_id end)) as "2003",
COUNT(distinct(case when year(appln_filing_date) = 2004 then tls201_appln.appln_id end)) as "2004",
COUNT(distinct(case when year(appln_filing_date) = 2005 then tls201_appln.appln_id end)) as "2005",
COUNT(distinct(case when year(appln_filing_date) = 2006 then tls201_appln.appln_id end)) as "2006",
COUNT(distinct(case when year(appln_filing_date) = 2007 then tls201_appln.appln_id end)) as "2007",
COUNT(distinct(case when year(appln_filing_date) = 2008 then tls201_appln.appln_id end)) as "2008",
COUNT(distinct(case when year(appln_filing_date) = 2009 then tls201_appln.appln_id end)) as "2009",
COUNT(distinct(case when year(appln_filing_date) = 2010 then tls201_appln.appln_id end)) as "2010",
COUNT(distinct(case when year(appln_filing_date) = 2011 then tls201_appln.appln_id end)) as "2011",
COUNT(distinct(case when year(appln_filing_date) = 2012 then tls201_appln.appln_id end)) as "2012",
COUNT(distinct(case when year(appln_filing_date) = 2013 then tls201_appln.appln_id end)) as "2013",
COUNT(distinct(case when year(appln_filing_date) = 2014 then tls201_appln.appln_id end)) as "2014",
COUNT(distinct(case when year(appln_filing_date) = 2015 then tls201_appln.appln_id end)) as "2015",
COUNT(distinct(case when year(appln_filing_date) = 2016 then tls201_appln.appln_id end)) as "2016",
COUNT(distinct(case when year(appln_filing_date) = 2017 then tls201_appln.appln_id end)) as "2017",
COUNT(distinct(case when year(appln_filing_date) = 2018 then tls201_appln.appln_id end)) as "2018",
COUNT(distinct(tls201_appln.appln_id )) as "total"


FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id =tls206_person.person_id
join tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id and left(ipc_class_symbol,3) in ('A01','C05')

where person_ctry_code = 'US' and applt_seq_nr >0 and invt_seq_nr = 0
and appln_auth in ('AT', 'BE', 'DK', 'FI', 'FR','DE', 'EL', 'EI', 'IT', 'NL', 'NO', 'PT', 'ES', 'SE', 'CH', 'UK') 
and appln_filing_year between 2000 and 2018
group by psn_name, left(ipc_class_symbol,3) 
order by psn_name, left(ipc_class_symbol,3)
And here is a slightly adapted version to cover all EU countries for EU applicants.

Code: Select all

SELECT psn_name, left(ipc_class_symbol,3) IPC,
COUNT(distinct(case when year(appln_filing_date) = 2000 then tls201_appln.appln_id end)) as "2000",
COUNT(distinct(case when year(appln_filing_date) = 2001 then tls201_appln.appln_id end)) as "2001",
COUNT(distinct(case when year(appln_filing_date) = 2002 then tls201_appln.appln_id end)) as "2002",
COUNT(distinct(case when year(appln_filing_date) = 2003 then tls201_appln.appln_id end)) as "2003",
COUNT(distinct(case when year(appln_filing_date) = 2004 then tls201_appln.appln_id end)) as "2004",
COUNT(distinct(case when year(appln_filing_date) = 2005 then tls201_appln.appln_id end)) as "2005",
COUNT(distinct(case when year(appln_filing_date) = 2006 then tls201_appln.appln_id end)) as "2006",
COUNT(distinct(case when year(appln_filing_date) = 2007 then tls201_appln.appln_id end)) as "2007",
COUNT(distinct(case when year(appln_filing_date) = 2008 then tls201_appln.appln_id end)) as "2008",
COUNT(distinct(case when year(appln_filing_date) = 2009 then tls201_appln.appln_id end)) as "2009",
COUNT(distinct(case when year(appln_filing_date) = 2010 then tls201_appln.appln_id end)) as "2010",
COUNT(distinct(case when year(appln_filing_date) = 2011 then tls201_appln.appln_id end)) as "2011",
COUNT(distinct(case when year(appln_filing_date) = 2012 then tls201_appln.appln_id end)) as "2012",
COUNT(distinct(case when year(appln_filing_date) = 2013 then tls201_appln.appln_id end)) as "2013",
COUNT(distinct(case when year(appln_filing_date) = 2014 then tls201_appln.appln_id end)) as "2014",
COUNT(distinct(case when year(appln_filing_date) = 2015 then tls201_appln.appln_id end)) as "2015",
COUNT(distinct(case when year(appln_filing_date) = 2016 then tls201_appln.appln_id end)) as "2016",
COUNT(distinct(case when year(appln_filing_date) = 2017 then tls201_appln.appln_id end)) as "2017",
COUNT(distinct(case when year(appln_filing_date) = 2018 then tls201_appln.appln_id end)) as "2018",
COUNT(distinct(tls201_appln.appln_id )) as "total"


FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id =tls206_person.person_id
join tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id and left(ipc_class_symbol,3) in ('A01','C05')

where person_ctry_code in (select ctry_code from tls801_country where eu_member  = 'Y')
and applt_seq_nr >0 and invt_seq_nr = 0
and appln_auth in  (select ctry_code from tls801_country where eu_member  = 'Y')
and appln_filing_year between 2000 and 2018
group by psn_name, left(ipc_class_symbol,3) 
order by psn_name, left(ipc_class_symbol,3)
Keep in mind that EP applications as such are not included in the list except for the EP member states that re-publish granted EP applications. For those re-publications, the data will be included in the figures.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


micvollaro
Posts: 3
Joined: Tue Oct 15, 2019 3:59 pm

Re: Number of patents per Country, per IPC code, per Year

Post by micvollaro » Thu Oct 17, 2019 5:04 pm

Thanks a lot!


Post Reply