Create three new tables: appln_ipc4 ipc4_nace appln_nace I'm running MySQL and I would use the MyISAM engine instead of InnoDB. Don't know what options you have on Microsoft Server. INSERT INTO `appln_ipc4` (`appln_id`, `ipc4`) SELECT DISTINCT `appln_id`, LEFT(`ipc_class_symbol`,4) FROM `tls209_appl...

Just pondering, in case the nb_applicants variable is bugged... MAX(applt_seq_nr) would probably also give you the number of applicants.

Do you have access to a local installation of PATSTAT or are you working with PATSTAT Online?
Are you looking at the full IPC class, or only at for example the subclass?

Which fields are available in your Italian Patent Office database?

I don't understand the question "The geographical coverage of relevant countries is excellent". What are relevant countries? Relevant for me? Relevant for the technology? As for the coverage, suppose only five countries in the world are patenting in wind energy, does that mean the geograph...

I do not have access to PATSTAT Online so I'm merely quoting the documentation (from my October 2014 version). Legal event data like "post grant" information for European patents in the national phase ("validation" data of EP patents) is available in the PRS database, also known ...

Hi Tim, When using harmonized names it is possible that you'll get a double count because a patent may have multiple assignees that when harmonized become the same entity. For example when Abbott Laboratories and Abbott Laboratories Chile are the assignees and the hrm_l2 says that both are "Abb...

I have been trying to rewrite the query to make it run faster but to no avail. I am afraid that your only option is to download data and perform the calculation somewhere else. You can not create views or tables in PATSTAT Online, nor can you create indices. The query you are trying to run is simply...

I'd do this in php and some intermediate tables, but I assume you are working with PATSTAT Online so that's not an option. I think you can leave out: "AND classA.ipc_subclass_symbol != 'NULL'" (because the INNER JOIN will drop all patents that have no IPC information) as well as ORDER BY c...

Sorry for the late reply. I was abroad. To answer "Count all applications by Firm X in Year Y", we first need to define what is Year Y. Let's use the YEAR(`tls201_appln`.`appln_filing_date`) for this. Then we need to define "by Firm X". I'm assuming you're looking for the applica...