Hello Lorens,
have a look here:
sql-queries-to-create-simple-applicant- ... able-10588
And in the PATSTAT Data Catalog you will find detailed information on the data base model and how the most used JOINS between the tables can be constructed.
Search found 440 matches
- Thu Apr 20, 2023 9:14 am
- Forum: PATSTAT Product Line
- Topic: IDs of applicants / inventors
- Replies: 1
- Views: 1000
- Tue Apr 18, 2023 12:15 pm
- Forum: PATSTAT Product Line
- Topic: Renewal vs Lapse
- Replies: 1
- Views: 6790
Re: Renewal vs Lapse
I would only use the PGFP as a proxy. It under-estimates indeed a couple of countries in very specific situations. AT, GB do not charge renewal fees before year 4,5,6 which means that some applications that were quickly granted will stay under the radar using PGFP. (IT does not charge the renewal fe...
- Wed Apr 12, 2023 2:36 pm
- Forum: PATSTAT Product Line
- Topic: Earliest and first kind of Priority Patents
- Replies: 2
- Views: 1036
Re: Earliest and first kind of Priority Patents
Hello schilma7, There are no "types of priorities" (with the small exceptions of utility models, design patents or plant patents -maybe there are some more...-). You linked the application to the publications and what you see are the publication instances of the patent applications. For EP...
- Tue Apr 11, 2023 3:04 pm
- Forum: PATSTAT Product Line
- Topic: Help with SQL query
- Replies: 1
- Views: 2217
Re: Help with SQL query
Hello illxh, there are several issues with your query, basically, it can not work (for several reasons). The main problem is that you try to make 1 SQL query that one way or another would result in all the data attributes you need (applicants, inventors, grant date, forward and backward citations, c...
- Thu Apr 06, 2023 4:27 pm
- Forum: PATSTAT Product Line
- Topic: Help with SQL query - How to count number of citation per year
- Replies: 3
- Views: 2006
Re: Help with SQL query - How to count number of citation per year
Hello khanhhienbi, your query does not count forward citations. On the forum there are quite some posts that explain the concept of forward citations. Looking at the WHERE clause of your query, I assume you want to count the forward citations of patents filed at the UK patent office that have been g...
- Thu Apr 06, 2023 3:13 pm
- Forum: PATSTAT Product Line
- Topic: Number of patents and patents applications per company
- Replies: 3
- Views: 5734
Re: Number of patents and patents applications per company
Hello Sina, in principle, that is possible, but you will need to develop a model/methodology to define which patents exactly you would consider ESG relevant. I fear that simply taking all patents tagged with cpc_class_symbol = 'Y02' or 'Y04S' will be too broad. You could see your question as a varia...
- Thu Apr 06, 2023 2:25 pm
- Forum: PATSTAT Product Line
- Topic: Extracting all swiss priority applications from applicants without duplicates
- Replies: 4
- Views: 1389
Re: Extracting all swiss priority applications from applicants without duplicates
Have a look at the NUTS codes in tls206_person and table tls904_nuts; it might save you some work.
- Thu Apr 06, 2023 2:21 pm
- Forum: PATSTAT Product Line
- Topic: Searching with docdb_family_id
- Replies: 1
- Views: 723
Re: Searching with docdb_family_id
Hello Lorens, All your observations are due to a small mistake in your query, you should have "FROM tls201_appln JOIN tls225_docdb_fam_cpc ON tls201_appln. docdb_family_id = tls225_docdb_fam_cpc. docdb_family_id ". (I left the mistake in your post to make it clear for others users.) Basica...
- Thu Mar 30, 2023 4:36 pm
- Forum: PATSTAT Product Line
- Topic: Primary and Secondary Patents
- Replies: 1
- Views: 4596
Re: Primary and Secondary Patents
Hello Hien, A second medical use (further medical use according to Article 54(5) EPC) means that the compound was used in the past as a medicament, but for the treatment of a different disease. From classification point of view, there is no possibility to distinguish first or further medical uses , ...
- Thu Mar 30, 2023 4:09 pm
- Forum: PATSTAT Product Line
- Topic: Count Patent applications that are granted and forward citation in specific year for specific company
- Replies: 3
- Views: 1178
Re: Count Patent applications that are granted and forward citation in specific year for specific company
That is a bit tricky because a family can have multiple granted applications filed in different years. But one can take the earliest_filing_year and count the families. The below query will give you the totals based on the earliest application filing year for each patent family member keeping in min...