Count of Utility Model Applications

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

lionpat
Posts: 7
Joined: Fri Oct 26, 2018 8:54 pm

Count of Utility Model Applications

Post by lionpat » Sat Dec 15, 2018 10:18 pm

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.


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

Re: Count of Utility Model Applications

Post by EPO / PATSTAT Support » Mon Dec 17, 2018 2:49 pm

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
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


lionpat
Posts: 7
Joined: Fri Oct 26, 2018 8:54 pm

Re: Count of Utility Model Applications

Post by lionpat » Tue Dec 18, 2018 2:59 am

Thank you very much! Your response is extremely helpful.


Post Reply