Search found 251 matches

by EPO / PATSTAT Support
Mon Jun 07, 2021 12:25 pm
Forum: PATSTAT Product Line
Topic: Collaborative and non-collaborative patents
Replies: 3
Views: 80

Re: Collaborative and non-collaborative patents

The queries are all variations on the same theme; in the WHERE part of the SQL query, you specify what "group" of patents the final result list should look like. The "AND tls201_appln.appln_id in (select.....)" defines intersections of samples of patents. So here is a query that selects all the pate...
by EPO / PATSTAT Support
Mon Jun 07, 2021 11:15 am
Forum: PATSTAT Product Line
Topic: Collaborative and non-collaborative patents
Replies: 3
Views: 80

Re: Collaborative and non-collaborative patents

Hello Isabel, here is a simple query that selects all EP patents (labelled "Chemistry") having at least 1 German University as applicant and nothing else. (Some patents have more then 1 German applicant that is labelled as a university: https://worldwide.espacenet.com/patent/search/family/038015913/...
by EPO / PATSTAT Support
Tue Jun 01, 2021 4:22 pm
Forum: PATSTAT Product Line
Topic: University Patents.
Replies: 2
Views: 172

Re: University Patents.

The reason is because your SQL query is build as such that the condition of "university" and "being German" is split in 2 different branches. So each of the patents will have a university (maybe not German) and a German applicant (maybe a university). It's only a matter of building up the query corr...
by EPO / PATSTAT Support
Tue May 25, 2021 9:58 am
Forum: PATSTAT Product Line
Topic: Help needed Annual patent count of specific firm
Replies: 2
Views: 111

Re: Help needed Annual patent count of specific firm

You can easily adapt one of the queries in this post to your needs: https://forums.epo.org/sql-queries-to-create-simple-applicant-year-based-table-10588 For example add: and psn_name in ('volkswagen' , 'renault' , 'audi' , 'VOLVO CAR CORPORATION' , 'PEUGEOT CITROEN AUTOMOBILES' , 'DAIMLER') in the W...
by EPO / PATSTAT Support
Fri May 21, 2021 4:09 pm
Forum: PATSTAT Product Line
Topic: Zip Code Column Empty
Replies: 1
Views: 253

Re: Zip Code Column Empty

Hello Rmudie96, the reason why your query does not return results is the missing spaces in the IPC codes. There should always be 8 characters before the position of the slash (which is position 9) The query below will give you data. However, keep in mind that a) addresses for physical persons have b...
by EPO / PATSTAT Support
Thu May 20, 2021 6:06 pm
Forum: PATSTAT Product Line
Topic: Missing abstract for US documents
Replies: 1
Views: 226

Re: Missing abstract for US documents

Hello Leonardo, thank you for pointing out the missing data. We have added it, but it will of course take until the next PATSTAT release before it comes available. At this link you can find an overview on coverage and missing data: https://forums.epo.org/mapping-data-completeness-of-patstat-global-a...
by EPO / PATSTAT Support
Tue May 18, 2021 3:27 pm
Forum: PATSTAT Product Line
Topic: Data import error of tls206_part02.csv
Replies: 6
Views: 591

Re: Data import error of tls206_part02.csv

Hello Adnan, here is the script that works fine on our MS SQL server to create the table: (from the scripts in the documentation): USE [patstat2021a] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tls206_person]( [person_id] [int] NOT NULL DEFAULT ('0'), [person_name] [nvarc...
by EPO / PATSTAT Support
Fri May 14, 2021 8:08 am
Forum: PATSTAT Product Line
Topic: Event_descr missing in 2021 Spring edition
Replies: 2
Views: 156

Re: Event_descr missing in 2021 Spring edition

Hello Eduardo, Indeed, the column is not included in tls231_inpadoc_legal_event in PATSTAT (but that is normal). You can link to tls803_legal_event_code via a join using the event_auth AND the event_code. Below is an example on how to do this. SELECT top 10 * from tls231_inpadoc_legal_event join tls...
by EPO / PATSTAT Support
Wed May 12, 2021 10:45 am
Forum: PATSTAT Product Line
Topic: Patent Fractional Count at NUTS-2 (3) level
Replies: 4
Views: 550

Re: Patent Fractional Count at NUTS-2 level

Here is a (non-fractional count) example using NUTS-3 level (assigned to the patent applicant(s) on the EP-B1 publication), for EP applications that were granted between 2005 and 2019. In the event of more then 1 applicant in different NUTS regions, each region will get "a count". If you want this i...
by EPO / PATSTAT Support
Wed Apr 28, 2021 5:31 pm
Forum: PATSTAT Product Line
Topic: SQL queries to create simple applicant / year based table.
Replies: 0
Views: 156

SQL queries to create simple applicant / year based table.

Many researchers are looking for tables giving applicant rankings based on the number of applications. With PATSTAT, there are endless possibilities to specify the "conditions" for those applicants (or patent applications). Here are 5 variations on the same theme that have all been tested on PATSTAT...