Page 1 of 1

Published patents by company

Posted: Mon Jul 16, 2018 3:37 am
by taques
I am trying to get on a range of published patents (cited.pat_publn_id) by company (taking TLS206_PERSON.PSN_SECTOR='COMPANY' as a main reference), from 1975 to 2018.
When I submit the script below, it returns few records from 2008 and 2009, and nothing before or after it.
Is there any record that my script is missing?

Best regards.

Here is it:

SELECT p_cited.psn_name, p_citing.psn_name, cited.publn_auth, citing.publn_auth, citing.publn_date, company.psn_sector, Count(cited.appln_id) AS CountOfappln_id
FROM tls206_person AS p_citing JOIN tls207_pers_appln AS pa_citing ON p_citing.person_id = pa_citing.person_id
INNER JOIN tls211_pat_publn AS citing ON pa_citing.appln_id = citing.appln_id
INNER JOIN tls212_citation ON citing.pat_publn_id = tls212_citation.pat_publn_id
INNER JOIN tls211_pat_publn AS cited ON tls212_citation.cited_pat_publn_id = cited.pat_publn_id
INNER JOIN tls224_appln_cpc AS CPC ON citing.appln_id = CPC.appln_id
INNER JOIN tls207_pers_appln ON cited.appln_id = tls207_pers_appln.appln_id
INNER JOIN tls206_person AS p_cited ON tls207_pers_appln.person_id = p_cited.person_id
INNER JOIN tls206_person AS company ON company.person_id = cited.pat_publn_id
WHERE Left(cpc_class_symbol,0)=''
AND tls207_pers_appln.applt_seq_nr = 1
AND pa_citing.applt_seq_nr =1
AND p_cited.psn_name <> p_citing.psn_name
AND company.psn_sector = 'COMPANY'
GROUP BY p_cited.psn_name, cited.publn_auth, p_citing.psn_name, citing.publn_auth, citing.publn_date, company.psn_sector
HAVING (((Count(cited.appln_id))>5))
ORDER BY citing.publn_date, p_cited.psn_name,Count(cited.appln_id) DESC;

Re: Published patents by company

Posted: Tue Jul 24, 2018 4:13 pm
by EPO / PATSTAT Support
I am not sure what you actually want to achieve. What I noted is that your last join has the join condition ‘company.person_id = cited.pat_publn_id’. You are comparing a person ID with a patent publication ID, which will get you random results.

It’s a complex query. I recommend you re-do it incrementally, adding a join or a filter condition each time and checking the plausibility of every intermediate result. This will reduce the risk that the result you get at the end is not what you think it is.