Hello Paolo, This query should do the job: select left(tls224_appln_cpc.cpc_class_symbol,11) as CPC, count(distinct(appln_id)) from tls224_appln_cpc where SUBSTRING(left(tls224_appln_cpc.cpc_class_symbol,11),9,3 )='/00' group by left(tls224_appln_cpc.cpc_class_symbol,11) order by left(tls224_appln_c...

@ curcitiz Additional to the previous replies, I would like to shortly explain how it can be done in PATSTAT on-line. Your question: 1) Publication number 2) Publication date year: patent from 2006 to 2010 included. 3) Name of the applicant (in my case will be the firm (example VESTAS). I know there...

ESPACENET allows to download of a list of Cited or Citing document from 1 specific patent. It does not allow you to download the "list" of a "list" of patents belonging to an applicant. So if your list of patents from an applicant only contains a "couple of dozens" of p...

Hello, your query is a ranking based on the application filing date. Generally spoken, there are 18 months between the date of the patent application and the publication of the application. Add to this 6 months (maximum) between the publication and the PATSTAT production date, and you will have betw...

Hello Paolo, if you mean by "main group". being the first 8 digits of the classification code then the following query will give you the result. select left(tls224_appln_cpc.cpc_class_symbol,8) as CPC_main_group, count(distinct(appln_id)) from tls224_appln_cpc group by left(tls224_appln_cp...

Hello, If it is effectively only half a dozen of patents, it might be quicker & easier to do it manually via ESPACENET: http://worldwide.espacenet.com ESPACENET allows you to download the cited (and also citing) documents as an excel or CSV file for any specific patent application. If you want t...

Hi everyone, Geert Boedt (EPO), Helene Dernis (OECD) and myself have written a 10-page introduction to the offline Patstat database. The article provides MySQL and SQL codes for several popular queries: - How to identify patent applications by technology field - How to identify PCT patent applicatio...

The question is not that easy to answer - or just very easy, depending how you look at it. Here are a couple of correct answers: PATSTAT contains ONLY data on applications that have been published (with a small exception of references to cited applications, for which dummy applications and publicati...

Dear PATSTAT users, It does not happen often, but we have to notify you about a deficiency in the current PATSTAT version. We have observed that about 4.5 per cent of the applications in the PATSTAT October 2013 release have their IPC codes missing. 99 % of the missing IPC codes belong to Japanese a...

Hello Joe, Unless you take a "distinct" first 4 digits of IPC codes, there will be duplications within a family. In the latest PATSTAT versions we have eliminated duplications within an application (on full IPC symbol). Yes it is possible that applications have different classifications ov...