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
Query by “type” of applicant
-
- Posts: 425
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Query by “type” of applicant
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
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
EPO - Vienna
patstat @ epo.org
-
- Posts: 4
- Joined: Sat Mar 17, 2018 1:24 pm
Re: Query by “type” of applicant
Hello,
Thank you!!
Telmo
Thank you!!
Telmo