University Patents.

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

Isabel C.
Posts: 7
Joined: Sat Apr 10, 2021 9:31 am

University Patents.

Post by Isabel C. » Tue Jun 01, 2021 2:34 pm

Hello,

I am a PhD Student in Economics, and I need some clarifications on some data I am downloading. I need to download all the French, Italian and German patents involving a university. Not collaborative patents but ALL patents.

I, therefore, used the following code:

Code: Select all

SELECT distinct  tls201_appln.appln_id, appln_auth,appln_nr, appln_filing_year, nb_applicants, nb_inventors, psn_name, tls206_person.NUTS, nuts_label , person_ctry_code, psn_sector, granted ,techn_field.techn_sector,techn_field.techn_field
  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 tls230_appln_techn_field on tls201_appln.appln_id = tls230_appln_techn_field.appln_id
  join (SELECT distinct techn_field_nr, techn_sector, techn_field FROM tls901_techn_field_ipc) techn_field
  on tls230_appln_techn_field.techn_field_nr = techn_field.techn_field_nr
  join tls904_nuts on tls206_person.nuts = tls904_nuts.nuts
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 
('UNIVERSITY'))
and applt_seq_nr > 0
and appln_filing_year between 1977 and 2018
and appln_auth = 'EP'
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 applt_seq_nr > 0 and person_ctry_code ='DE')
and techn_sector = 'Chemistry'
order by appln_filing_year, tls201_appln.appln_id
Nonetheless, when I checked the data, I noticed that ONLY for Italy and Germany, data also include patents from Company-Company. Do you have any idea why this happens?

Best,

I.C.


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

Re: University Patents.

Post by EPO / PATSTAT Support » Tue Jun 01, 2021 4:22 pm

The reason is because your SQL query is build as such that the condition of "university" and "being German" is split in 2 different branches. So each of the patents will have a university (maybe not German) and a German applicant (maybe a university).
It's only a matter of building up the query correctly.

In it's easiest form (according to your criteria), you could use the query below.
Because 1 application can be in more then 1 technology field (within the Chemistry sector), I grouped the fields in 1 attribute.

Code: Select all

SELECT distinct  tls201_appln.appln_id, appln_auth,appln_nr, appln_filing_year, nb_applicants, nb_inventors,
psn_name, tls206_person.NUTS, nuts_label , person_ctry_code, psn_sector, granted ,
STRING_AGG(CAST(techn_field.techn_field AS NVARCHAR(MAX)), CHAR(13) + CHAR(10)) techn_fields
  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 tls230_appln_techn_field on tls201_appln.appln_id = tls230_appln_techn_field.appln_id
  join (SELECT distinct techn_field_nr, techn_sector, techn_field FROM tls901_techn_field_ipc) techn_field
  on tls230_appln_techn_field.techn_field_nr = techn_field.techn_field_nr
  join tls904_nuts on tls206_person.nuts = tls904_nuts.nuts
WHERE psn_sector = 'UNIVERSITY'
and applt_seq_nr > 0
and appln_filing_year between 1977 and 2018
and appln_auth = 'EP'
and person_ctry_code ='DE'
and techn_sector = 'Chemistry'
group by tls201_appln.appln_id, appln_auth,appln_nr, appln_filing_year, nb_applicants, nb_inventors,
psn_name, tls206_person.NUTS, nuts_label , person_ctry_code, psn_sector, granted
order by appln_filing_year, tls201_appln.appln_id
Geert BOEDT
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Isabel C.
Posts: 7
Joined: Sat Apr 10, 2021 9:31 am

Re: University Patents.

Post by Isabel C. » Thu Jun 03, 2021 11:38 am

Thank you so much for your very clear answer! :)


Post Reply