Thanks for the reply, my query code is shown below.
I have tried retrieving data from 1980 to 1985, with two IPC limits added. Such an attempt gave successful results, so I expanded the query code to what it is now.
My target result is worldwide companies, so I have not added appln_auth, person_ctry_code or community limits. To get more results I removed the "nb_citing_docdb_fam > 0" limit.
Code: Select all
SELECT psn_id, psn_name, LEFT(ipc_class_symbol,3) IPC,
COUNT(distinct(case when appln_filing_year = 1980 then a.nb_citing_docdb_fam end)) as "1980",
COUNT(distinct(case when appln_filing_year = 1981 then a.nb_citing_docdb_fam end)) as "1981",
COUNT(distinct(case when appln_filing_year = 1982 then a.nb_citing_docdb_fam end)) as "1982",
COUNT(distinct(case when appln_filing_year = 1983 then a.nb_citing_docdb_fam end)) as "1983",
COUNT(distinct(case when appln_filing_year = 1984 then a.nb_citing_docdb_fam end)) as "1984",
COUNT(distinct(case when appln_filing_year = 1985 then a.nb_citing_docdb_fam end)) as "1985",
COUNT(distinct(case when appln_filing_year = 1986 then a.nb_citing_docdb_fam end)) as "1986",
COUNT(distinct(case when appln_filing_year = 1987 then a.nb_citing_docdb_fam end)) as "1987",
COUNT(distinct(case when appln_filing_year = 1988 then a.nb_citing_docdb_fam end)) as "1988",
COUNT(distinct(case when appln_filing_year = 1989 then a.nb_citing_docdb_fam end)) as "1989",
COUNT(distinct(case when appln_filing_year = 1990 then a.nb_citing_docdb_fam end)) as "1990",
COUNT(distinct(case when appln_filing_year = 1991 then a.nb_citing_docdb_fam end)) as "1991",
COUNT(distinct(case when appln_filing_year = 1992 then a.nb_citing_docdb_fam end)) as "1992",
COUNT(distinct(case when appln_filing_year = 1993 then a.nb_citing_docdb_fam end)) as "1993",
COUNT(distinct(case when appln_filing_year = 1994 then a.nb_citing_docdb_fam end)) as "1994",
COUNT(distinct(case when appln_filing_year = 1995 then a.nb_citing_docdb_fam end)) as "1995",
COUNT(distinct(case when appln_filing_year = 1996 then a.nb_citing_docdb_fam end)) as "1996",
COUNT(distinct(case when appln_filing_year = 1997 then a.nb_citing_docdb_fam end)) as "1997",
COUNT(distinct(case when appln_filing_year = 1998 then a.nb_citing_docdb_fam end)) as "1998",
COUNT(distinct(case when appln_filing_year = 1999 then a.nb_citing_docdb_fam end)) as "1999",
COUNT(distinct(case when appln_filing_year = 2000 then a.nb_citing_docdb_fam end)) as "2000",
COUNT(distinct(case when appln_filing_year = 2001 then a.nb_citing_docdb_fam end)) as "2001",
COUNT(distinct(case when appln_filing_year = 2002 then a.nb_citing_docdb_fam end)) as "2002",
COUNT(distinct(case when appln_filing_year = 2003 then a.nb_citing_docdb_fam end)) as "2003",
COUNT(distinct(case when appln_filing_year = 2004 then a.nb_citing_docdb_fam end)) as "2004",
COUNT(distinct(case when appln_filing_year = 2005 then a.nb_citing_docdb_fam end)) as "2005",
COUNT(distinct(case when appln_filing_year = 2006 then a.nb_citing_docdb_fam end)) as "2006",
COUNT(distinct(case when appln_filing_year = 2007 then a.nb_citing_docdb_fam end)) as "2007",
COUNT(distinct(case when appln_filing_year = 2008 then a.nb_citing_docdb_fam end)) as "2008",
COUNT(distinct(case when appln_filing_year = 2009 then a.nb_citing_docdb_fam end)) as "2009",
COUNT(distinct(case when appln_filing_year = 2010 then a.nb_citing_docdb_fam end)) as "2010",
COUNT(distinct(case when appln_filing_year = 2011 then a.nb_citing_docdb_fam end)) as "2011",
COUNT(distinct(case when appln_filing_year = 2012 then a.nb_citing_docdb_fam end)) as "2012",
COUNT(distinct(case when appln_filing_year = 2013 then a.nb_citing_docdb_fam end)) as "2013",
COUNT(distinct(case when appln_filing_year = 2014 then a.nb_citing_docdb_fam end)) as "2014",
COUNT(distinct(case when appln_filing_year = 2015 then a.nb_citing_docdb_fam end)) as "2015",
COUNT(distinct(case when appln_filing_year = 2016 then a.nb_citing_docdb_fam end)) as "2016",
COUNT(distinct(case when appln_filing_year = 2017 then a.nb_citing_docdb_fam end)) as "2017",
COUNT(distinct(case when appln_filing_year = 2018 then a.nb_citing_docdb_fam end)) as "2018",
COUNT(distinct(case when appln_filing_year = 2019 then a.nb_citing_docdb_fam end)) as "2019",
COUNT(distinct(case when appln_filing_year = 2020 then a.nb_citing_docdb_fam end)) as "2020",
COUNT(distinct(case when appln_filing_year = 2021 then a.nb_citing_docdb_fam end)) as "2021",
COUNT(distinct(case when appln_filing_year = 2022 then a.nb_citing_docdb_fam end)) as "2022",
COUNT(distinct(a.nb_citing_docdb_fam)) as "total"
FROM tls201_appln a
Join tls207_pers_appln b on a.appln_id = b.appln_id
Join tls206_person c on b.person_id = c.person_id
Join tls801_country on c.person_ctry_code= tls801_country.ctry_code
Join tls209_appln_ipc d on a.appln_id = d.appln_id
WHERE
appln_filing_year between 1980 and 2022
And granted = 'Y'
And LEFT(ipc_class_symbol,3) in ('A01','A43','A61','A62','B01','B03','B09','B22','B60','B61','B62','B63','B64','B65','C01','C02','C04','C05','C07','C08','C09','C10','C11','C12','C14','C21','C23','C25','C30','D01','D21','E02','E03','E04','E06','E21','F01','F02','F03','F16','F21','F22','F23','F24','F25','F26','F27','F28','G01','G02','G05','G06','G08','G21','H01','H02','H05') --add 3-digit IPC here
And applt_seq_nr > 0 and invt_seq_nr = 0
And psn_sector = 'company'
GROUP BY psn_id, psn_name, LEFT(ipc_class_symbol,3)
ORDER BY psn_id, psn_name, LEFT(ipc_class_symbol,3)