at the moment I am conducting an empirical work for which I need all filed patents at nuts-3 level, for Italy, for years from 1978 to 2015 (with them I subsequently create a fractional patent count with Stata).
So far I have successfully used the following query, running it individually for each year:
Code: Select all
SELECT APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, APP.appln_nr_epodoc, APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year, APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, P.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, N.nuts, N.nuts_level, N.nuts_label, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant, PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
FROM tls904_nuts N
join tls206_person P on N.nuts = P.nuts
join tls207_pers_appln PA on P.person_id = PA.person_id
join tls201_appln APP on PA.appln_id = APP.appln_id
join tls209_appln_ipc APIPC on APP.appln_id = APIPC.appln_id
join tls226_person_orig PO on PA.person_id = PO.person_id
join tls202_appln_title APT on APP.appln_id = APT.appln_id
join tls211_pat_publn PPB on APP.appln_id = PPB.appln_id
join tls229_appln_nace2 NA2 on APP.appln_id = NA2.appln_id
join tls230_appln_techn_field ATF on APP.appln_id = ATF.appln_id
join tls212_citation CIT on PPB.pat_publn_id = CIT.pat_publn_id
WHERE N.nuts like 'IT%'
AND N.nuts_level=3
AND APP.appln_filing_date >= '2015-01-01'
AND APP.appln_filing_date <= '2015-12-31'
GROUP BY N.nuts, N.nuts_level, N.nuts_label, APP.appln_id, APP.appln_auth, APP.appln_nr, APP.appln_kind, APP.appln_filing_date, APP.appln_filing_year, APP.appln_nr_epodoc, APP.appln_nr_original, APP.ipr_type, APP.receiving_office, APP.internat_appln_id, APP.int_phase, APP.reg_phase, APP.nat_phase, APP.earliest_filing_date, APP.earliest_filing_year, APP.earliest_filing_id, APP.earliest_publn_date, APP.earliest_publn_year, APP.earliest_pat_publn_id, APP.granted, APP.docdb_family_id, APP.inpadoc_family_id, APP.docdb_family_size, APP.nb_citing_docdb_fam, APP.nb_applicants, APP.nb_inventors, APT.appln_title, P.person_id, P.person_name, P.person_address, P.person_ctry_code, P.doc_std_name_id, P.doc_std_name, P.psn_id, P.psn_name, P.psn_level, P.psn_sector, P.han_id, P.han_name, P.han_harmonized, PA.applt_seq_nr, PA.invt_seq_nr, PO.city, PO.zip_code, PO.person_ctry_code, PO.residence_ctry_code, PO.role, APIPC.ipc_class_symbol, APIPC.ipc_class_level, APIPC.ipc_version, APIPC.ipc_value, APIPC.ipc_position, APIPC.ipc_gener_auth, NA2.nace2_code, NA2.weight, ATF.techn_field_nr, ATF.weight, PPB.pat_publn_id, PPB.publn_auth, PPB.publn_nr, PPB.publn_kind, PPB.publn_date, PPB.publn_first_grant, PPB.publn_claims, CIT.citn_replenished, CIT.citn_id, CIT.citn_origin, CIT.cited_pat_publn_id, CIT.cited_appln_id, CIT.pat_citn_seq_nr, CIT.cited_npl_publn_id, CIT.npl_citn_seq_nr, CIT.citn_gener_auth
Thank you very much to everyone