Page 1 of 1

patents smart grids by company

Posted: Wed Apr 12, 2017 7:12 pm
by Maly
Hi I’m new to Patstat, I need help, please !
I would like to extract all the patents (patent family) filed in the technology Smart Grids (Y04S) , by company and country (Germany, Japan, France and Danemark). Is that possible ? Do you have some idea for the query, please ? (Can I add the number of citations)

Re: patents smart grids by company

Posted: Tue Apr 18, 2017 4:27 pm
by Geert Boedt
Hello Maly,
If you want to research on all patents that have been classified with Y04S, you will have a rather big sample of data. The best might be to make a data base extraction via PATSTAT Online, and then continue any data aggregation on your own local computer. (You need MS ACCESS or any other data management system that can handle relational data.)
Extracting that sample is straightforward: you can use this SQL query.

Code: Select all

SELECT distinct appln_id from tls224_appln_cpc where left(cpc_class_symbol,4) = 'Y04S'
The query above will give you a set of 50.127 patent applications (in PATSTAT 2016 AUTUMN), which you then can expand with the family members or the cited and citing applications, depending on the nature of your research.

If you only want a pure list with the patent application data, as well as the applicant names and the number of citations (family/family based), then you could use the SQL query below. This will give you a result table with 76.225 records. This is more then the total number of applications because applications that have more then 1 applicant will occur multiple times. The result can also be downloaded for further analysis or visualisation.

Code: Select all

SELECT distinct tls201_appln.appln_id, appln_auth, appln_nr, appln_kind, appln_filing_date, granted, docdb_family_id, docdb_family_size, nb_citing_docdb_fam, applt_seq_nr ,psn_name, person_ctry_code
from tls201_appln join tls224_appln_cpc on tls201_appln.appln_id = tls224_appln_cpc.appln_id
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
where left(cpc_class_symbol,4) = 'Y04S' and applt_seq_nr > 0
order by appln_auth, appln_filing_date, tls201_appln.appln_id, applt_seq_nr