Query by “type” of applicant

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

TelmoSantos
Posts: 4
Joined: Sat Mar 17, 2018 1:24 pm

Query by “type” of applicant

Post by TelmoSantos » Sat Mar 17, 2018 1:35 pm

Dear PATSTAT community,

I am writing a research project and for one of the topics I would like to list the patent applications that include more than one applicant, in which at least one is a private organization (for instance a company) and at least one is a public R&D entity (for instance one university). Is it possible to do query by “type” of applicants?

Thank you very much for your help.
Telmo


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

Re: Query by “type” of applicant

Post by EPO / PATSTAT Support » Tue Mar 20, 2018 5:09 pm

Hello Telmo,
in the tls206_person table you will find an attribute called PSN_SECTOR.
This sector-isation approach is described in this document:
https://circabc.europa.eu/sd/a/5b474c9c ... ation.pdf

Your criteria can be easily translated into an sql query like the one below.
You might want to adapt the criteria in the WHERE clause, as well as reducing/adapting the 2 groups of psn_sectors in the 2 subqueries

Code: Select all

SELECT tls201_appln.appln_id, appln_auth,appln_nr, appln_kind, appln_filing_date,appln_nr_epodoc,
tls206_person.person_id,person_name, psn_name, person_address ,person_ctry_code, psn_sector
  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
WHERE nb_applicants > 1
and 
tls201_appln.appln_id in 
(select appln_id from tls207_pers_appln join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id where psn_sector in  
		('COMPANY',
		'COMPANY GOV NON-PROFIT',
		'COMPANY GOV NON-PROFIT UNIVERSITY',
		'COMPANY HOSPITAL',
		'COMPANY UNIVERSITY'))
and
tls201_appln.appln_id in 
(select appln_id from tls207_pers_appln join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id where psn_sector in  
		('GOV NON-PROFIT UNIVERSITY',
		'HOSPITAL',
		'UNIVERSITY',
		'UNIVERSITY HOSPITAL'
		))
and applt_seq_nr > 0
and appln_filing_year > 2000
and appln_auth = 'EP'
order by tls201_appln.appln_id
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


TelmoSantos
Posts: 4
Joined: Sat Mar 17, 2018 1:24 pm

Re: Query by “type” of applicant

Post by TelmoSantos » Thu Mar 22, 2018 8:40 pm

Hello,
Thank you!!
Telmo


Post Reply