EPO - Discussion forums

Posted: **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.

Posted: **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.

EPO - Discussion forums

How to query for joint patents?

How to query for joint patents?

Re: How to query for joint patents?