1) Using code from some of the other discussion posts I am using the following SQL code for patents on PV using "cpc_class_symbol like 'Y02E 10/5%'":
Code: Select all
SELECT person_ctry_code,
COUNT(distinct(case when appln_filing_year = 2000 then tls201_appln.appln_id end)) as '2000',
COUNT(distinct(case when appln_filing_year = 2001 then tls201_appln.appln_id end)) as '2001',
COUNT(distinct(case when appln_filing_year = 2002 then tls201_appln.appln_id end)) as '2002',
COUNT(distinct(case when appln_filing_year = 2003 then tls201_appln.appln_id end)) as '2003',
COUNT(distinct(case when appln_filing_year = 2004 then tls201_appln.appln_id end)) as '2004',
COUNT(distinct(case when appln_filing_year = 2005 then tls201_appln.appln_id end)) as '2005',
COUNT(distinct(case when appln_filing_year = 2006 then tls201_appln.appln_id end)) as '2006',
COUNT(distinct(case when appln_filing_year = 2007 then tls201_appln.appln_id end)) as '2007',
COUNT(distinct(case when appln_filing_year = 2008 then tls201_appln.appln_id end)) as '2008',
COUNT(distinct(case when appln_filing_year = 2009 then tls201_appln.appln_id end)) as '2009',
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_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 tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
AND appln_filing_year between 2000 and 2020
and applt_seq_nr > 0
and granted = 'y'
and (tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc WHERE cpc_class_symbol like 'Y02E 10/5%'))
group by person_ctry_code
order by total desc
Code: Select all
SELECT person_ctry_code,
COUNT(distinct(case when appln_filing_year = 2000 then tls201_appln.appln_id end)) as '2000',
COUNT(distinct(case when appln_filing_year = 2001 then tls201_appln.appln_id end)) as '2001',
COUNT(distinct(case when appln_filing_year = 2002 then tls201_appln.appln_id end)) as '2002',
COUNT(distinct(case when appln_filing_year = 2003 then tls201_appln.appln_id end)) as '2003',
COUNT(distinct(case when appln_filing_year = 2004 then tls201_appln.appln_id end)) as '2004',
COUNT(distinct(case when appln_filing_year = 2005 then tls201_appln.appln_id end)) as '2005',
COUNT(distinct(case when appln_filing_year = 2006 then tls201_appln.appln_id end)) as '2006',
COUNT(distinct(case when appln_filing_year = 2007 then tls201_appln.appln_id end)) as '2007',
COUNT(distinct(case when appln_filing_year = 2008 then tls201_appln.appln_id end)) as '2008',
COUNT(distinct(case when appln_filing_year = 2009 then tls201_appln.appln_id end)) as '2009',
COUNT(distinct(case when appln_filing_year = 2010 then tls201_appln.appln_id end)) as '2010',
COUNT(distinct(case when appln_filing_year = 2011 then tls201_appln.appln_id end)) as '2011',
COUNT(distinct(case when appln_filing_year = 2012 then tls201_appln.appln_id end)) as '2012',
COUNT(distinct(case when appln_filing_year = 2013 then tls201_appln.appln_id end)) as '2013',
COUNT(distinct(case when appln_filing_year = 2014 then tls201_appln.appln_id end)) as '2014',
COUNT(distinct(case when appln_filing_year = 2015 then tls201_appln.appln_id end)) as '2015',
COUNT(distinct(case when appln_filing_year = 2016 then tls201_appln.appln_id end)) as '2016',
COUNT(distinct(case when appln_filing_year = 2017 then tls201_appln.appln_id end)) as '2017',
COUNT(distinct(case when appln_filing_year = 2018 then tls201_appln.appln_id end)) as '2018',
COUNT(distinct(case when appln_filing_year = 2019 then tls201_appln.appln_id end)) as '2019',
COUNT(distinct(case when appln_filing_year = 2020 then tls201_appln.appln_id end)) as '2020',
count (tls201_appln.appln_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 tls801_country on tls206_person.person_ctry_code = tls801_country.ctry_code
AND appln_filing_year between 2000 and 2020
and applt_seq_nr > 0
and granted = 'y'
and (tls201_appln.appln_id in (select distinct appln_id from tls224_appln_cpc WHERE cpc_class_symbol like 'Y02E 10/5%'))
group by person_ctry_code
order by total desc
Later I might change this to subgroups of Y02E 10/5 (541-549) and offshore and onshore wind turbines (Y02E 10727-728)
My main problem is that I noticed there is a very large number of patents where no country code is available for the applicant. Is this due to the information available for applicant, the use of the Y02E classification, or something else which I could address to decrease this number?
Many thanks in advance and happy for any suggestions