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!
Number of patents per Country, per IPC code, per Year
-
- Posts: 3
- Joined: Tue Oct 15, 2019 3:59 pm
-
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Number of patents per Country, per IPC code, per Year
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 ?
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
EPO - Vienna
patstat @ epo.org
-
- Posts: 3
- Joined: Tue Oct 15, 2019 3:59 pm
Re: Number of patents per Country, per IPC code, per Year
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.
Hope that this clarifies. Thanks.
-
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Number of patents per Country, per IPC code, per Year
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.
And here is a slightly adapted version to cover all EU countries for EU applicants.
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.
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)
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)
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org
EPO - Vienna
patstat @ epo.org
-
- Posts: 3
- Joined: Tue Oct 15, 2019 3:59 pm