EPO - Discussion forums

Posted: **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.

Posted: **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

Posted: **Thu Jun 03, 2021 11:38 am**

Thank you so much for your very clear answer!

EPO - Discussion forums

University Patents.

University Patents.

Re: University Patents.

Re: University Patents.