Please help a student to make a query : D

[signekkkkk]

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

[EPO / PATSTAT Support]

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 ?

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