Published patents by company
Posted: Mon Jul 16, 2018 3:37 am
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;
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;