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 ;
--
Exclude specific company name
-
- Posts: 176
- Joined: Tue Oct 19, 2004 10:36 am
- Location: Vienna
Re: Exclude specific company name
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:
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.
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:
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%'
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%'
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
Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna