Exclude specific company name

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

Patstatnewby
Posts: 1
Joined: Thu Nov 30, 2017 12:04 pm

Exclude specific company name

Post by Patstatnewby » Thu Nov 30, 2017 12:07 pm

Hi everyone,

I recently started working with Patstat for my thesis. I use the query below.

The problem is now, I need all the patents from Amgen inc. However, when I include 'Amgen%', this also lists the patents of 'Amgen Fremont' which is another company. How can I specifically exclude this company from the results when using the query?

Many thanks!!!!

---

SELECT persons.company ,
COUNT(distinct(case when appln_filing_year between 2008 and 2011 then ap.docdb_family_id end)) as "2008-2011",
COUNT(distinct(case when appln_filing_year between 2008 and 2011 and granted = 1 then ap.docdb_family_id end)) as "2008-2011_is_granted",
COUNT(distinct(case when appln_filing_year between 2013 and 2016 then ap.docdb_family_id end)) as "2013-2016",
COUNT(distinct(case when appln_filing_year between 2013 and 2016 and granted = 1 then ap.docdb_family_id end)) as "2013-2016_is_granted"
FROM tls201_appln as ap JOIN tls207_pers_appln ON ap.appln_id = tls207_pers_appln.appln_id
JOIN (select (case when psn_name like 'CSR%' then 'CSR'
when psn_name like 'Dynex%' then 'Dynex' end) as "company", person_id from tls206_person WHERE (psn_name like 'CSR%' or psn_name like 'Dynex') ) persons ON tls207_pers_appln.person_id = persons.person_id
WHERE appln_filing_year between '2008' and '2016'
GROUP BY persons.company -- creates totals per name
ORDER BY persons.company ;

--


Geert Boedt
Posts: 176
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Exclude specific company name

Post by Geert Boedt » Thu Nov 30, 2017 3:51 pm

Hello Newby,
when you make use of the standardised name (PSN attribute) in combination with wild cards, it is a good approach to do a quick sanity check to see whether your wildcard does not include (or exclude) too many results.
In your case you could simply run the following query to get a list of possible candidates:

Code: Select all

SELECT distinct psn_name, person_name , person_ctry_code
  FROM tls206_person 
  where psn_name like 'Amgen%'  
Now that you know that you want to exclude some of the names, you can expand the conditions in the WHERE clause to EXCLUDE names.

Code: Select all

SELECT distinct psn_name, person_name , person_ctry_code
  FROM tls206_person 
 where psn_name like 'Amgen%' and psn_name not like 'Amgen%FR%'  
I am sure you want to exclude some more names. An altenative is that you narrow down the wild card and simply list the names of the applicants you want to be included.
It all boils down to defining the best set of conditions to narrow down to your wanted sample.

The final adapted query could be something like this:

Code: Select all

SELECT persons.company ,
 COUNT(distinct(case when appln_filing_year between 2008 and 2011 then ap.docdb_family_id end)) as "2008-2011",
 COUNT(distinct(case when appln_filing_year between 2008 and 2011 and granted = 1 then ap.docdb_family_id end)) as "2008-2011_is_granted",
 COUNT(distinct(case when appln_filing_year between 2013 and 2016 then ap.docdb_family_id end)) as "2013-2016",
 COUNT(distinct(case when appln_filing_year between 2013 and 2016 and granted = 1 then ap.docdb_family_id end)) as "2013-2016_is_granted"
 FROM tls201_appln as ap JOIN tls207_pers_appln ON ap.appln_id = tls207_pers_appln.appln_id
 JOIN (select (case when psn_name like 'CSR%' then 'CSR'
		when psn_name like 'Dynex%' then 'Dynex' 
		when (psn_name like  'Amgen%' and psn_name not like 'Amgen%FR%') then 'AMGEN'  end) as "company", person_id from tls206_person 
		WHERE (psn_name like 'CSR%' or psn_name like 'Dynex' or (psn_name like  'Amgen%' and psn_name not like 'Amgen%FR%')))  persons ON tls207_pers_appln.person_id = persons.person_id
 WHERE appln_filing_year between '2008' and '2016'
 GROUP BY persons.company -- creates totals per name
 ORDER BY persons.company ;
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


Post Reply