Applicant and Inventor Country of Residence

[browst]

Hello,

I am trying and struggling to retrieve information about patents which have an applicant(s) residing in one country and an inventor(s) residing in another country, for research related to my master's thesis. I have been using the very helpful information that Geert provided on this forum line, post15286.html#p15286 however I cannot seem to figure out how to retrieve country information for both inventors and applicants on a given application. Anyone have any information on how to do this?

Additionally, I am using PATSTAT online, as I can only afford the free 2 month trial period, and I am aware that inventor and applicant's addresses are not available on PATSTAT online.

Thanks

Nick

[Geert Boedt]

Hello Nick,
it is correct that addresses for physical persons have been removed, but not the country codes.
The question is what kind of information you want to have from those applications. I assume you want to do some further data aggregation; such as comparing pairs, looking for frequencies of pairs, ...
The query below will give you the applicant-inventor pairs that have different country codes.
As an application can have multiple applicants and inventors, from different countries, you will have multiple rows (records) per application. The query will exclude the applications where all applicants and inventors have the same country. So depending on what you need, you might have to adapt the query. (for example excluding the "blank" country codes.) If your data sample is not too big, you might consider simply adapting the query to only retrieve the tls201_appln.appln_id and then download a PATSTAT subset including the person table to rest of your data aggregation.

Code: Select all

SELECT distinct  tls201_appln.appln_id, appln_auth, appln_nr, appln_kind, appln_filing_date, appln_filing_year 
      , appln_nr_epodoc,nb_applicants, nb_inventors
	  ,applicant.person_name applt_name, applicant.person_ctry_code applt_ctry
	  ,inventor.person_name  invt_name , inventor.person_ctry_code invt_ctry
FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id =tls207_pers_appln.appln_id
	join (SELECT distinct tls207_pers_appln.appln_id, tls206_person.person_id, person_name, person_ctry_code 
			FROM tls206_person join tls207_pers_appln 
			on tls206_person.person_id = tls207_pers_appln.person_id and applt_seq_nr > 0 ) applicant 
	on tls201_appln.appln_id = applicant.appln_id

	join (SELECT distinct tls207_pers_appln.appln_id, tls206_person.person_id, person_name, person_ctry_code 
			FROM tls206_person join tls207_pers_appln 
			on tls206_person.person_id = tls207_pers_appln.person_id and invt_seq_nr > 0 ) inventor
	on tls201_appln.appln_id = inventor.appln_id
where tls201_appln.appln_id between 2 and 20  and applicant.person_ctry_code <> inventor.person_ctry_code
order by tls201_appln.appln_id, applicant.person_name

If you look at the result of the above query, you will observe that for appln_id 16 you have a FR and CH applicant, and also FR and CH inventors. The FR applicant in combination with the CH inventor triggers a row, as well as the CH applicant with the FR inventor. Dependant on what you want to research on, you might have to further exclude those cases. But it will not be easy to do this in a "one run sql query". (Maybe some PATSTAT users have more elegant sollutions ?)