How to count forward citations by company
Posted: Thu Nov 30, 2017 5:32 pm
Hi everyone,
For my research work, I would like to count the number of forward citations of companies' granted patents for a given window period. In particular, my aim is to use a 9-year moving window starting at 2000. For instance, I would like to get all the forward citations patents that are granted at 2000 receive during the period 2001-2009, then, the forward citations patents that are granted at 2001 receive over the period 2002-2010, and so on. I would like to gather this information by each company in the pharmaceuticals industry and for patents granted at the USPTO. I would like to move the window from 2000 to 2016.
I have prepared a query for my purposes, and I would like to receive feedback from you to validate it. I have elaborated on several examples previously proposed in this forum.
My query for patents granted at 2000 that counts forward citations for the period 2001-2009 is like this:
select t1.appln_id, person_name, count(distinct t3.pat_publn_id) as cited_year
from tls201_appln t1
join
(select appln_id, publn_date as granted_date
from tls211_pat_publn
group by appln_id, publn_date) t2 on t1.appln_id=t2.appln_id
join tls211_pat_publn t2b on t2.appln_id=t2b.appln_id
join tls212_citation t3 on t2b.pat_publn_id=t3.cited_pat_publn_id
join tls211_pat_publn t4 on t3.pat_publn_id=t4.pat_publn_id
join tls207_pers_appln t5 on t1.appln_id = t5.appln_id
join tls206_person t6 on t5.person_id = t6.person_id
where t2b.publn_auth='US'
and appln_kind ='A'
and year(t2.granted_date) != 9999
and t2.granted_date >= '2000-01-01'
and t2.granted_date <= '2000-12-31'
and t2b.publn_first_grant=1
and DATEDIFF(year, t2.granted_date, '2009-12-31') <=9
and (person_name like '22nd Century Group Inc%'
or person_name like '3-Dimensional Pharmaceutical%'
or person_name like '3SBio Inc%'
or person_name like 'Abbott Laboratories%'
or person_name like 'AbbVie Inc%'
or person_name like 'Abeona Therapeutics Inc%'
or person_name like 'Abgenix Inc%'
or person_name like 'Able Laboratories Inc%'
or person_name like 'Abraxis BioScience Inc%'
or person_name like 'AC Immune SA%'
or person_name like 'Acacia Diversified Holdings Inc%'
or person_name like 'Acadia Pharmaceuticals Inc%')
group by t1.appln_id, person_name
I appreciate any comment/feedback you can provide about my query
Best wishes,
Abel Lucena, PhD
For my research work, I would like to count the number of forward citations of companies' granted patents for a given window period. In particular, my aim is to use a 9-year moving window starting at 2000. For instance, I would like to get all the forward citations patents that are granted at 2000 receive during the period 2001-2009, then, the forward citations patents that are granted at 2001 receive over the period 2002-2010, and so on. I would like to gather this information by each company in the pharmaceuticals industry and for patents granted at the USPTO. I would like to move the window from 2000 to 2016.
I have prepared a query for my purposes, and I would like to receive feedback from you to validate it. I have elaborated on several examples previously proposed in this forum.
My query for patents granted at 2000 that counts forward citations for the period 2001-2009 is like this:
select t1.appln_id, person_name, count(distinct t3.pat_publn_id) as cited_year
from tls201_appln t1
join
(select appln_id, publn_date as granted_date
from tls211_pat_publn
group by appln_id, publn_date) t2 on t1.appln_id=t2.appln_id
join tls211_pat_publn t2b on t2.appln_id=t2b.appln_id
join tls212_citation t3 on t2b.pat_publn_id=t3.cited_pat_publn_id
join tls211_pat_publn t4 on t3.pat_publn_id=t4.pat_publn_id
join tls207_pers_appln t5 on t1.appln_id = t5.appln_id
join tls206_person t6 on t5.person_id = t6.person_id
where t2b.publn_auth='US'
and appln_kind ='A'
and year(t2.granted_date) != 9999
and t2.granted_date >= '2000-01-01'
and t2.granted_date <= '2000-12-31'
and t2b.publn_first_grant=1
and DATEDIFF(year, t2.granted_date, '2009-12-31') <=9
and (person_name like '22nd Century Group Inc%'
or person_name like '3-Dimensional Pharmaceutical%'
or person_name like '3SBio Inc%'
or person_name like 'Abbott Laboratories%'
or person_name like 'AbbVie Inc%'
or person_name like 'Abeona Therapeutics Inc%'
or person_name like 'Abgenix Inc%'
or person_name like 'Able Laboratories Inc%'
or person_name like 'Abraxis BioScience Inc%'
or person_name like 'AC Immune SA%'
or person_name like 'Acacia Diversified Holdings Inc%'
or person_name like 'Acadia Pharmaceuticals Inc%')
group by t1.appln_id, person_name
I appreciate any comment/feedback you can provide about my query
Best wishes,
Abel Lucena, PhD