EPO - Discussion forums

Post your opinions, discuss, share information and knowledge about the European Patent Office's tools and services.
https://forums.epo.org/

Count of Utility Model Applications

https://forums.epo.org/viewtopic.php?t=7979

Page 1 of 1

Count of Utility Model Applications

Posted: Sat Dec 15, 2018 10:18 pm
by lionpat
Dear PatStat Support:

I am trying to formulate two searches. First, is to obtain a list of the top twenty utility model applicants globally per year from 2000-2017. Second is to obtain the top twenty utility model applicants in particular application authorities (for example, cn, de, fr, es, tw) per year from 2000-2017. I understand how to obtain a full list of all such applicants. But I cannot figure out how to put them in order by number of applications. Can you help me with the code?

In addition, I would like to distinguish between domestic and non-domestic applicants in each authority. However, it appears that the ctry_code information is incomplete and may not permit this last query.

Thank you for any assistance you can render.

Re: Count of Utility Model Applications

Posted: Mon Dec 17, 2018 2:49 pm
by EPO / PATSTAT Support
Hell lionpat,
your question is rather an SQL- syntax problem then a PATSTAT question.
Here is a query that gives you the top 20 UM filers in Germany per year, based on their total number of UM applications.

Code: Select all

SELECT top 20 psn_name, 
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(tls201_appln.appln_id) total
FROM tls201_appln 
  join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
  join tls906_person on tls207_pers_appln.person_id = tls906_person.person_id
where ipr_type = 'UM' and appln_filing_year between 2000 and 2017 and appln_auth = 'DE'
group by psn_name
order by total desc

Code: Select all

SELECT top 20 psn_name, 
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(tls201_appln.appln_id) total
FROM tls201_appln 
  join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
  join tls906_person on tls207_pers_appln.person_id = tls906_person.person_id
where ipr_type = 'UM' and appln_filing_year between 2000 and 2017 and appln_auth = 'CN' and applt_seq_nr > 0 and person_ctry_code <> 'CN'
group by psn_name
order by total desc
And above is an example for China, where we have very little applicant country information.
So the person_ctry_code <> 'CN' condition only has a minimal effect because most of the values are blank.

Doing this for 'TW' gives a more different picture:

Code: Select all

SELECT top 20 psn_name, tls906_person.person_ctry_code,
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(tls201_appln.appln_id) total
FROM tls201_appln 
  join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
  join tls906_person on tls207_pers_appln.person_id = tls906_person.person_id
where ipr_type = 'UM' and appln_filing_year between 2000 and 2017 and appln_auth = 'TW' and applt_seq_nr > 0 and person_ctry_code <> 'TW'
group by psn_name,tls906_person.person_ctry_code
order by total desc

Re: Count of Utility Model Applications

Posted: Tue Dec 18, 2018 2:59 am
by lionpat
Thank you very much! Your response is extremely helpful.
All times are UTC+01:00
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/