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
(select appln_id, publn_date as granted_date
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
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 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
Abel Lucena, PhD
Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
1 post • Page 1 of 1