I need some help to improve a code that I am using to retrieve ALL French patents involving universities (both collaborative and non-collaborative) from 1978 to 2018. We will have to replicate the same thing for Germany and Italy, for universities and gov non-profit.
To get the collaborative patents, I am using 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, docdb_family_size, nb_citing_docdb_fam
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
([b]'UNIVERSITY',
'UNIVERSITY GOV NON-PROFIT',
'UNIVERSITY GOV NON-PROFIT COMPANY',
'UNIVERSITY HOSPITAL',
'UNIVERSITY UNIVERSITY'[/b]))
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
('[b]GOV NON-PROFIT UNIVERSITY',
'HOSPITAL',
'UNIVERSITY',
'UNIVERSITY HOSPITAL'[/b]
))
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 ='FR')
and techn_sector = 'Chemistry'
order by appln_filing_year, tls201_appln.appln_id
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_sector,
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, techn_sector
order by appln_filing_year, tls201_appln.appln_id
Last but not least, we would like to get information on the patent family and see where those patents are protected and its quality. We assume that the higher the countries' number where a patent is protected, the higher its economic value. I used docdb_family_size and nb_citing_docdb_fam to get this information. If I am right, the former corresponds to the size of the family of a given application. The same invention disclosed by a common inventor is patented in more than one country. The latter indicates the number of forward citations (simple families citing at least one of the publications or applications of the DOCDB simple family of the current application). Hence, it should be a good indicator of the quality and impact of the patent. Is this correct as a proxy for our assumption or would you have any other suggestion?
On the contrary, granted is assumed to indicate the novelty of the discovery.
Sorry for this very long question. Hope you could help us improve our work.
Best,
I.C.