Dear Discussion Forum
I am student looking for patent data for my master thesis and I am unexperienced in SQL. Is there therefore anyone for whom it is not hard to make a query that could help me?
I have the free trial, but I need a query that helps download all patents filed for and granted in Europe along with their their CPC classification and the applicants (name of firm, country code and address).
Thank you so much in advance.
Sincerely, Signe
Please help a student to make a query : D
-
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Please help a student to make a query : D
Such query would generate more then 2 million records, even when only taking the "first applicant" in account for those applications where there are multiple applicants.
Here is a query that gives you the results for application filed in 2015. The free trial lets you only download 10.000 rows. A better approach is maybe to use SQL to aggregate the data to what you really need ?
Here is a query that gives you the results for application filed in 2015. The free trial lets you only download 10.000 rows. A better approach is maybe to use SQL to aggregate the data to what you really need ?
Code: Select all
SELECT a.appln_id,appln_auth,appln_nr,appln_filing_date, granted,
CPC_grouped,psn_name,person_address, person_ctry_code, psn_sector
FROM tls201_appln a
JOIN (select docdb_family_id , STRING_AGG (cast((cpc_class_symbol)as NVARCHAR(MAX)), '¦ ') CPC_grouped from tls225_docdb_fam_cpc
where cpc_position <> 'L' group by docdb_family_id) d ON a.docdb_family_id = d.docdb_family_id
JOIN tls207_pers_appln f ON a.appln_id = f.appln_id
JOIN tls206_person g ON f.person_id = g.person_id
WHERE appln_auth= 'EP'
AND ipr_type = 'PI'
AND applt_seq_nr = 1
and granted= 'Y'
and appln_filing_year = 2015
group by a.appln_id,appln_auth,appln_nr,appln_filing_date, granted,
CPC_grouped,psn_name,person_address, person_ctry_code, psn_sector
ORDER BY a.appln_filing_date desc ,a.appln_id
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org
EPO - Vienna
patstat @ epo.org