Just coming back to the query above, in order to map technological development in the wind industry, I'd like to check for patent filings of Y02E 10/70 (ideally actually two separate for Y02E 10/728 and Y02E 10/727 to distinguish between the shift from onshore to offshore, but general wind patents would also be ok for) in combination with digital technologies or energy storage (such as G06 or H01M) or hybrid systems (H02S 10/12).
I've updated the search query which seems to work but returns 0 results. Am I making a mistake somewhere? The CPC code combinations are taken from a recently published study so I would expect there to be existing applications using these combinations.
Code: Select all
SELECT person_ctry_code,
COUNT(distinct(case when appln_filing_year = 1980 then tls201_appln.appln_id end)) as '1980',
COUNT(distinct(case when appln_filing_year = 1981 then tls201_appln.appln_id end)) as '1981',
COUNT(distinct(case when appln_filing_year = 1982 then tls201_appln.appln_id end)) as '1982',
COUNT(distinct(case when appln_filing_year = 1983 then tls201_appln.appln_id end)) as '1983',
COUNT(distinct(case when appln_filing_year = 1984 then tls201_appln.appln_id end)) as '1984',
COUNT(distinct(case when appln_filing_year = 1985 then tls201_appln.appln_id end)) as '1985',
COUNT(distinct(case when appln_filing_year = 1986 then tls201_appln.appln_id end)) as '1986',
COUNT(distinct(case when appln_filing_year = 1987 then tls201_appln.appln_id end)) as '1987',
COUNT(distinct(case when appln_filing_year = 1988 then tls201_appln.appln_id end)) as '1988',
COUNT(distinct(case when appln_filing_year = 1989 then tls201_appln.appln_id end)) as '1989',
COUNT(distinct(case when appln_filing_year = 1990 then tls201_appln.appln_id end)) as '1990',
COUNT(distinct(case when appln_filing_year = 1991 then tls201_appln.appln_id end)) as '1991',
COUNT(distinct(case when appln_filing_year = 1992 then tls201_appln.appln_id end)) as '1992',
COUNT(distinct(case when appln_filing_year = 1993 then tls201_appln.appln_id end)) as '1993',
COUNT(distinct(case when appln_filing_year = 1994 then tls201_appln.appln_id end)) as '1994',
COUNT(distinct(case when appln_filing_year = 1995 then tls201_appln.appln_id end)) as '1995',
COUNT(distinct(case when appln_filing_year = 1996 then tls201_appln.appln_id end)) as '1996',
COUNT(distinct(case when appln_filing_year = 1997 then tls201_appln.appln_id end)) as '1997',
COUNT(distinct(case when appln_filing_year = 1998 then tls201_appln.appln_id end)) as '1998',
COUNT(distinct(case when appln_filing_year = 1999 then tls201_appln.appln_id end)) as '1999',
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 1980 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/70%'
AND (cpc_class_symbol LIKE 'G06%'
OR cpc_class_symbol LIKE 'H021M%')
OR cpc_class_symbol LIKE 'H01S 10/12%'))
group by person_ctry_code
order by total desc
If I may, I would also like to connect this to another question. As the next step I would like to see how many of these 'high-tech' patents specific German firms in the wind industry have filed during the same time. For simplicity, let's just take the biggest ones 'Enercon', 'Siemens', and 'Nordex.
Could I simply replace person_ctry_code with psn_name and use the following adjusted code?
Code: Select all
SELECT psn_name,
COUNT(distinct(case when appln_filing_year = 1980 then tls201_appln.appln_id end)) as '1980',
COUNT(distinct(case when appln_filing_year = 1981 then tls201_appln.appln_id end)) as '1981',
COUNT(distinct(case when appln_filing_year = 1982 then tls201_appln.appln_id end)) as '1982',
COUNT(distinct(case when appln_filing_year = 1983 then tls201_appln.appln_id end)) as '1983',
COUNT(distinct(case when appln_filing_year = 1984 then tls201_appln.appln_id end)) as '1984',
COUNT(distinct(case when appln_filing_year = 1985 then tls201_appln.appln_id end)) as '1985',
COUNT(distinct(case when appln_filing_year = 1986 then tls201_appln.appln_id end)) as '1986',
COUNT(distinct(case when appln_filing_year = 1987 then tls201_appln.appln_id end)) as '1987',
COUNT(distinct(case when appln_filing_year = 1988 then tls201_appln.appln_id end)) as '1988',
COUNT(distinct(case when appln_filing_year = 1989 then tls201_appln.appln_id end)) as '1989',
COUNT(distinct(case when appln_filing_year = 1990 then tls201_appln.appln_id end)) as '1990',
COUNT(distinct(case when appln_filing_year = 1991 then tls201_appln.appln_id end)) as '1991',
COUNT(distinct(case when appln_filing_year = 1992 then tls201_appln.appln_id end)) as '1992',
COUNT(distinct(case when appln_filing_year = 1993 then tls201_appln.appln_id end)) as '1993',
COUNT(distinct(case when appln_filing_year = 1994 then tls201_appln.appln_id end)) as '1994',
COUNT(distinct(case when appln_filing_year = 1995 then tls201_appln.appln_id end)) as '1995',
COUNT(distinct(case when appln_filing_year = 1996 then tls201_appln.appln_id end)) as '1996',
COUNT(distinct(case when appln_filing_year = 1997 then tls201_appln.appln_id end)) as '1997',
COUNT(distinct(case when appln_filing_year = 1998 then tls201_appln.appln_id end)) as '1998',
COUNT(distinct(case when appln_filing_year = 1999 then tls201_appln.appln_id end)) as '1999',
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 1980 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/70%'
AND (cpc_class_symbol LIKE 'G06%'
OR cpc_class_symbol LIKE 'H021M%')
OR cpc_class_symbol LIKE 'H01S 10/12%'))
AND psn_name like '%Enercon%' OR '%Siemens%' OR '%Nordex%'
GROUP BY psn_name
ORDER BY total desc
Many thanks for your help as always!