1. I am uncertain if the IPC classifications for biotechnology, namely A01H, A61K, C40B, C12N, C12P, C12Q, C12S, G01N are the appropriate and updated ones to use. It would be great if you could confirm.
2. I initially wanted to look at the IPC classification C12N in my research. I then tried to expand my search to cover the whole field with the rest of the IPC classifications mentioned above. I then realised that some of the patent applications have more than one IPC classification. For example, a company can have 5 applications in 2015 under C12N but only 1 in the same year under C12Q. How should I know if a company filed one patent application or filed a total of six patent applications with different classifications (based on the example)?
3. Should the SQL queries be modified to retrieve better results?
Code: Select all
SELECT psn_name,
COUNT(distinct(case when t1.appln_filing_year = 2011 then t1.appln_id end)) as '2011',
COUNT(distinct(case when t1.appln_filing_year = 2012 then t1.appln_id end)) as '2012',
COUNT(distinct(case when t1.appln_filing_year = 2013 then t1.appln_id end)) as '2013',
COUNT(distinct(case when t1.appln_filing_year = 2014 then t1.appln_id end)) as '2014',
COUNT(distinct(case when t1.appln_filing_year = 2015 then t1.appln_id end)) as '2015',
COUNT(distinct(case when t1.appln_filing_year = 2016 then t1.appln_id end)) as '2016',
COUNT(distinct(case when t1.appln_filing_year = 2017 then t1.appln_id end)) as '2017',
COUNT(distinct(case when t1.appln_filing_year = 2018 then t1.appln_id end)) as '2018',
COUNT(distinct(case when t1.appln_filing_year = 2019 then t1.appln_id end)) as '2019',
COUNT(distinct(case when t1.appln_filing_year = 2019 then t1.appln_id end)) as '2020',
count(t1.appln_id) total
FROM tls201_appln t1 JOIN tls209_appln_ipc t2
ON t1.appln_id = t2.appln_id
join tls207_pers_appln on t1.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
WHERE
t2.ipc_class_symbol LIKE 'C12N%' --replace with the rest of the IPC classifications mentioned above
AND t1.appln_auth IN ('GB')
AND t1.appln_filing_year between 2010 and 2019
and applt_seq_nr > 0 and invt_seq_nr = 0
group by psn_name
order by total desc