How to query for joint patents?

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

kendels
Posts: 1
Joined: Sat Jul 06, 2013 10:05 pm

How to query for joint patents?

Post by kendels » Sat Jul 06, 2013 10:22 pm

Hello,

I am new to SQL and PATSTAT and trying to conduct a query for my thesis. Hopefully I am getting support here.

What do I want to query?

I want to do a query where I get: "All patent applications of a company X where there are more than 1 applicants (companies) in a specific time span"

For example: I want an answer to the question: How many patents did a company X apply together with other companies from 2005 to 2011".

Am I right with the statement, that one invention can be patented in several countries? If this is true, I just want to measure new inventions.

Regarding that, I found that it is not that easy searching for a specific company, because for example Samsung has a lot of subsidiaries which are applicating patents. Drawing on another post in this forum, I would maybe try to do the following query which is giving the organizations with most patent applications with the word for e.g. Samsung in it:

Code: Select all

select tls208_doc_std_nms.doc_std_name_id, tls208_doc_std_nms.doc_std_name, COUNT(*)
FROM tls201_appln
INNER JOIN tls207_pers_appln ON tls201_appln.appln_id=tls207_pers_appln.appln_id 
INNER JOIN tls206_person ON tls207_pers_appln.person_id=tls206_person.person_id
INNER JOIN tls208_doc_std_nms ON tls206_person.doc_std_name_id=tls208_doc_std_nms.doc_std_name_id
WHERE tls208_doc_std_nms.doc_std_name LIKE '%Samsung%'
GROUP BY tls208_doc_std_nms.doc_std_name_id
ORDER BY COUNT(*) DESC;
Then I would pick the top 5 results for each company and try to answer the question mentioned above.

If you have any other suggestions on how to do this query - feel free to answer. Meanwhile I am trying to get an solution myself.

Really looking forward to an answer.


nico.rasters
Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm
Contact:

Re: How to query for joint patents?

Post by nico.rasters » Fri Nov 01, 2013 10:39 am

Apologies for the late reply. This forum really needs to implement a message notification system.

Anyway, your question actually consists of several parts.

To query which patents had more than one applicant:
SELECT DISTINCT `APPLN_ID` FROM `TLS207_PERS_APPLN` WHERE `APPLT_SEQ_NR`>1;
I find 4,504,411 total (Query took 0.0027 sec) in PATSTAT October 2011.
You could insert these results into a new table. Let's assume this table is called MULT_APPLT.
INSERT INTO `MULT_APPLT` (`APPLN_ID`) SELECT DISTINCT `APPLN_ID` FROM `TLS207_PERS_APPLN` WHERE `APPLT_SEQ_NR`>1;
Make sure that the `APPLN_ID` field is a PRIMARY KEY and that its datatype is UNSIGNED INT.

To narrow down the timespan, you can use the TLS201_APPLN table:
SELECT `TLS201_APPLN`.`APPLN_ID` FROM `TLS201_APPLN` INNER JOIN `MULT_APPLT` ON `TLS201_APPLN`.`APPLN_ID` = `MULT_APPLT`.`APPLN_ID` WHERE YEAR(`APPLN_FILING_DATE`) BETWEEN 2005 AND 2011;

But you want only those patents that were applied for by Samsung.
You can use the `TLS208_DOC_STD_NAMES` table, but this will not give you an overview of Samsung's affiliates. For this you will need a database such as Who Owns Whom or Corporate Affiliations. Or you can check Samsung's year reports. Alternatively there's Croctail: http://croctail.corpwatch.org/#cw_7871,cw_7871,2012. Croctail derives its data from http://www.sec.gov/cgi-bin/browse-edgar ... getcompany

You need to create an historical overview of Samsung's affiliates. Suppose Acme Corp was an affiliate from 2002-2004. Then you probably only want to count patents from Acme Corp that were applied for between 2002-2004.
Finding "Acme Corp" patents can be done in the same way as for Samsung (by use of the `TLS208_DOC_STD_NAMES` table). Other options are mentioned in my article http://www.datagnostics.net/oh_abbott_w ... t_thou.htm. I actually have to update this article, but the main point is that there are four ways: `TLS206_PERSON`, `TLS208_DOC_STD_NMS`, `EEE_PPAT` and `HAN_PERSON`. The latter two are external sources which you can get for free.

You will also need to deal with patent families... but I would suggest that you take care of the above steps first before complicating matters.

Hope this helps you on your way.
________________________________________
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship
http://www.daigu.nl/


Post Reply