Hello khanhhienbi,
your query does not count forward citations. On the forum there are quite some posts that explain the concept of forward citations.
Looking at the WHERE clause of your query, I assume you want to count the forward citations of patents filed at the UK patent office that have been granted. (and classified techn_field_nr = 15).
So via the tls212_citation table, you count the number of publications that would cite the GB-granted- tech.field 15 applications.) Aggregated on the application filing year(s).
Here is a re-worked query:
Code: Select all
SELECT psn_name,
COUNT((case when appln_filing_year = 2008 then tls212_citation.pat_publn_id end)) as '2008',
COUNT((case when appln_filing_year = 2009 then tls212_citation.pat_publn_id end)) as '2009',
COUNT((case when appln_filing_year = 2010 then tls212_citation.pat_publn_id end)) as '2010',
COUNT((case when appln_filing_year = 2011 then tls212_citation.pat_publn_id end)) as '2011',
COUNT((case when appln_filing_year = 2012 then tls212_citation.pat_publn_id end)) as '2012',
COUNT((case when appln_filing_year = 2013 then tls212_citation.pat_publn_id end)) as '2013',
COUNT((case when appln_filing_year = 2014 then tls212_citation.pat_publn_id end)) as '2014',
COUNT((case when appln_filing_year = 2015 then tls212_citation.pat_publn_id end)) as '2015',
COUNT((case when appln_filing_year = 2016 then tls212_citation.pat_publn_id end)) as '2016',
COUNT((case when appln_filing_year = 2017 then tls212_citation.pat_publn_id end)) as '2017',
COUNT((case when appln_filing_year = 2018 then tls212_citation.pat_publn_id end)) as '2018',
COUNT((case when appln_filing_year = 2019 then tls212_citation.pat_publn_id end)) as '2019',
COUNT((case when appln_filing_year = 2020 then tls212_citation.pat_publn_id end)) as '2020',
COUNT((case when appln_filing_year = 2021 then tls212_citation.pat_publn_id end)) as '2021',
count ((tls212_citation.pat_publn_id)) total
FROM tls201_appln JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
JOIN tls206_person ON tls207_pers_appln.person_id = tls206_person.person_id
JOIN tls211_pat_publn ON tls201_appln.appln_id = tls211_pat_publn.appln_id
JOIN tls212_citation ON tls211_pat_publn.pat_publn_id = tls212_citation.cited_pat_publn_id
WHERE
appln_auth = 'GB'
and applt_seq_nr > 0
and granted = 'y'
and tls201_appln.appln_id in (select appln_id from tls230_appln_techn_field where tls230_appln_techn_field.techn_field_nr = 15)
and appln_filing_year between 2008 and 2021
group by psn_name
order by total desc
And to explicitly illustrate where the data comes from for each applicant,
here is the query that shows all the data records used for psn_name = 'UNIVERSITY OF CALIFORNIA'. (number 12 in the result list from the above query.)
The above query tells us that there are 22 citations for applications filed in 2013 by psn_name = 'UNIVERSITY OF CALIFORNIA' in techn_field_nr = 15. (For 2014 there are 2 citations).
The query below illustrates in detail the list of those 22+2 citations.
Code: Select all
SELECT distinct tls201_appln.appln_id,tls201_appln.appln_auth + tls201_appln.appln_nr cited_application,
tls201_appln.appln_filing_year, psn_name, tls211_pat_publn.pat_publn_id,
tls211_pat_publn.publn_auth+tls211_pat_publn.publn_nr+tls211_pat_publn.publn_kind cited_publication,
tls211_pat_publn.publn_date, tls212_citation.cited_pat_publn_id, tls212_citation.pat_publn_id,
citing.publn_auth+citing.publn_nr+citing.publn_kind citing_publication, citing.publn_date citing_publication_date
FROM tls201_appln JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
JOIN tls206_person ON tls207_pers_appln.person_id = tls206_person.person_id
JOIN tls211_pat_publn ON tls201_appln.appln_id = tls211_pat_publn.appln_id
JOIN tls212_citation ON tls211_pat_publn.pat_publn_id = tls212_citation.cited_pat_publn_id
join tls211_pat_publn citing on tls212_citation.pat_publn_id = citing.pat_publn_id
WHERE
appln_auth = 'GB'
and applt_seq_nr > 0
and granted = 'y'
and tls201_appln.appln_id in (select appln_id from tls230_appln_techn_field where tls230_appln_techn_field.techn_field_nr = 15)
and appln_filing_year between 2008 and 2021
and psn_name = 'UNIVERSITY OF CALIFORNIA'
order by appln_filing_year, tls201_appln.appln_id, tls212_citation.pat_publn_id
So from the details you can see that for filing year 2013, 4 patent applications were cited by 22 other patent publications. For 2014, 1 application was cited by 2 patent publication.
Generally spoken, I am not sure why one would specifically count the forward citations of patents filed at the UK patent office.