Count Patent based on Assignee Country
Posted: Tue Oct 09, 2018 12:51 am
Daer PATSTAT Support,
I em experiencing difficulty when I try to perform counting patents based on Assignee Country. For example, when I tried to find all the patent issued by the Japanese Patent Office to Japanese companies in 2005, I used the following code:
select a1.appln_id, tls206_person.person_ctry_code
--COUNT(distinct(case when tls211_pat_publn.publn_date = 2005 then ap.appln_id end)) as "2005"
from tls201_appln a1
left join tls207_pers_appln tls207_pers_appln on a1.appln_id = tls207_pers_appln.appln_id
left join tls206_person tls206_person on tls206_person.person_id = tls207_pers_appln.person_id
left join tls211_pat_publn a2 on a2.appln_id = a1.appln_id
where a2.publn_auth = 'JP'
and a2.publn_date between '2005-01-01' and '2005-12-31'
--and tls206_person.person_ctry_code = 'JP'
and a2.publn_first_grant = '1'
Specifically, when I select tls206_person.person_ctry_code, the returned country code column in the table was almost blank. Is it because the information was not reported to the database? Or is is because my code is wrong?
Best regards,
Murry
I em experiencing difficulty when I try to perform counting patents based on Assignee Country. For example, when I tried to find all the patent issued by the Japanese Patent Office to Japanese companies in 2005, I used the following code:
select a1.appln_id, tls206_person.person_ctry_code
--COUNT(distinct(case when tls211_pat_publn.publn_date = 2005 then ap.appln_id end)) as "2005"
from tls201_appln a1
left join tls207_pers_appln tls207_pers_appln on a1.appln_id = tls207_pers_appln.appln_id
left join tls206_person tls206_person on tls206_person.person_id = tls207_pers_appln.person_id
left join tls211_pat_publn a2 on a2.appln_id = a1.appln_id
where a2.publn_auth = 'JP'
and a2.publn_date between '2005-01-01' and '2005-12-31'
--and tls206_person.person_ctry_code = 'JP'
and a2.publn_first_grant = '1'
Specifically, when I select tls206_person.person_ctry_code, the returned country code column in the table was almost blank. Is it because the information was not reported to the database? Or is is because my code is wrong?
Best regards,
Murry