Hello Tom,
it will not be so easy to do this in 1 batch for 260 companies due to the fact that PATSTAT contains data from nearly 100 patent offices and multiple "variations" on applicant names for big companies. (Even in the PSN harmonised name attribute.)
My suggestion for a possible approach (there might be better ones...)
First have a look at the variations of the names. If you work across multiple filing authorities, you probably will need to make use of wildcards in the names and even the harmonised names will not group everything in ready-to-use chunks.
If we take the example for Sanofi Aventis Inc (acquirer) - Schering Plough-Plant (target) ,and I assume you want to see "some kind of change or effect", the following query will give you for all name variations the number of patent applications as well as the number of those applications being granted.
Code: Select all
SELECT psn_name,
COUNT(distinct(case when appln_filing_year = 2006 then ap.appln_id end)) as "2006",
COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then ap.appln_id end)) as "2006_is_granted",
COUNT(distinct(case when appln_filing_year = 2007 then ap.appln_id end)) as "2007",
COUNT(distinct(case when appln_filing_year = 2007 and granted = 1 then ap.appln_id end)) as "2007_is_granted",
COUNT(distinct(case when appln_filing_year = 2008 then ap.appln_id end)) as "2008",
COUNT(distinct(case when appln_filing_year = 2008 and granted = 1 then ap.appln_id end)) as "2008_is_granted",
COUNT(distinct(case when appln_filing_year = 2009 then ap.appln_id end)) as "2009",
COUNT(distinct(case when appln_filing_year = 2009 and granted = 1 then ap.appln_id end)) as "2009_is_granted",
COUNT(distinct(case when appln_filing_year = 2010 then ap.appln_id end)) as "2010",
COUNT(distinct(case when appln_filing_year = 2010 and granted = 1 then ap.appln_id end)) as "2010_is_granted",
COUNT(distinct(case when appln_filing_year = 2011 then ap.appln_id end)) as "2011",
COUNT(distinct(case when appln_filing_year = 2011 and granted = 1 then ap.appln_id end)) as "2011_is_granted",
COUNT(distinct(case when appln_filing_year = 2012 then ap.appln_id end)) as "2012",
COUNT(distinct(case when appln_filing_year = 2012 and granted = 1 then ap.appln_id end)) as "2012_is_granted",
COUNT(distinct(case when appln_filing_year = 2013 then ap.appln_id end)) as "2013",
COUNT(distinct(case when appln_filing_year = 2013 and granted = 1 then ap.appln_id end)) as "2013_is_granted",
COUNT(distinct(case when appln_filing_year = 2014 then ap.appln_id end)) as "2014",
COUNT(distinct(case when appln_filing_year = 2014 and granted = 1 then ap.appln_id end)) as "2014_is_granted"
FROM tls201_appln as ap JOIN tls207_pers_appln ON ap.appln_id = tls207_pers_appln.appln_id
JOIN tls206_person ON tls207_pers_appln.person_id = tls206_person.person_id
WHERE psn_name like 'Sanofi%' or psn_name like 'Schering%Plough%' and appln_filing_year between '2006' and '2014'
GROUP BY psn_name -- creates totals per name/country combination
ORDER BY psn_name
That gives you enough detail to see what is happening or what needs to be cleaned or grouped.
Assume you decide that all the names of those applicants are to be considered, and you want to group it now over 2 grouped applicants: ; then you could use the following query.
Code: Select all
SELECT persons.company ,
COUNT(distinct(case when appln_filing_year = 2006 then ap.appln_id end)) as "2006",
COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then ap.appln_id end)) as "2006_is_granted",
COUNT(distinct(case when appln_filing_year = 2007 then ap.appln_id end)) as "2007",
COUNT(distinct(case when appln_filing_year = 2007 and granted = 1 then ap.appln_id end)) as "2007_is_granted",
COUNT(distinct(case when appln_filing_year = 2008 then ap.appln_id end)) as "2008",
COUNT(distinct(case when appln_filing_year = 2008 and granted = 1 then ap.appln_id end)) as "2008_is_granted",
COUNT(distinct(case when appln_filing_year = 2009 then ap.appln_id end)) as "2009",
COUNT(distinct(case when appln_filing_year = 2009 and granted = 1 then ap.appln_id end)) as "2009_is_granted",
COUNT(distinct(case when appln_filing_year = 2010 then ap.appln_id end)) as "2010",
COUNT(distinct(case when appln_filing_year = 2010 and granted = 1 then ap.appln_id end)) as "2010_is_granted",
COUNT(distinct(case when appln_filing_year = 2011 then ap.appln_id end)) as "2011",
COUNT(distinct(case when appln_filing_year = 2011 and granted = 1 then ap.appln_id end)) as "2011_is_granted",
COUNT(distinct(case when appln_filing_year = 2012 then ap.appln_id end)) as "2012",
COUNT(distinct(case when appln_filing_year = 2012 and granted = 1 then ap.appln_id end)) as "2012_is_granted",
COUNT(distinct(case when appln_filing_year = 2013 then ap.appln_id end)) as "2013",
COUNT(distinct(case when appln_filing_year = 2013 and granted = 1 then ap.appln_id end)) as "2013_is_granted",
COUNT(distinct(case when appln_filing_year = 2014 then ap.appln_id end)) as "2014",
COUNT(distinct(case when appln_filing_year = 2014 and granted = 1 then ap.appln_id end)) as "2014_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 'Sanofi%' then 'Sanofi'
when psn_name like 'Schering%Plough%' then 'Schering_Plough' end) as "company", person_id from tls206_person WHERE (psn_name like 'Sanofi%' or psn_name like 'Schering%Plough%') ) persons ON tls207_pers_appln.person_id = persons.person_id
WHERE appln_filing_year between '2006' and '2014'
GROUP BY persons.company -- creates totals per name/country combination
ORDER BY persons.company ;
And the same query, but then grouped per year interval:
Code: Select all
SELECT persons.company ,
COUNT(distinct(case when appln_filing_year between 2006 and 2009 then ap.appln_id end)) as "2006-2009",
COUNT(distinct(case when appln_filing_year between 2006 and 2009 and granted = 1 then ap.appln_id end)) as "2006-2009_is_granted",
COUNT(distinct(case when appln_filing_year between 2010 and 2014 then ap.appln_id end)) as "2010-2014",
COUNT(distinct(case when appln_filing_year between 2010 and 2014 and granted = 1 then ap.appln_id end)) as "2010-2014_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 'Sanofi%' then 'Sanofi'
when psn_name like 'Schering%Plough%' then 'Schering_Plough' end) as "company", person_id from tls206_person WHERE (psn_name like 'Sanofi%' or psn_name like 'Schering%Plough%') ) persons ON tls207_pers_appln.person_id = persons.person_id
WHERE appln_filing_year between '2006' and '2014'
GROUP BY persons.company -- creates totals per name
ORDER BY persons.company ;
And the same approach but counting patent families instead of applications; this will reduce the numbers because applications filed for the same invention, but in different countries will be considered as 1 count:
Code: Select all
SELECT persons.company ,
COUNT(distinct(case when appln_filing_year between 2006 and 2009 then ap.docdb_family_id end)) as "2006-2009",
COUNT(distinct(case when appln_filing_year between 2006 and 2009 and granted = 1 then ap.docdb_family_id end)) as "2006-2009_is_granted",
COUNT(distinct(case when appln_filing_year between 2010 and 2014 then ap.docdb_family_id end)) as "2010-2014",
COUNT(distinct(case when appln_filing_year between 2010 and 2014 and granted = 1 then ap.docdb_family_id end)) as "2010-2014_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 'Sanofi%' then 'Sanofi'
when psn_name like 'Schering%Plough%' then 'Schering_Plough' end) as "company", person_id from tls206_person WHERE (psn_name like 'Sanofi%' or psn_name like 'Schering%Plough%') ) persons ON tls207_pers_appln.person_id = persons.person_id
WHERE appln_filing_year between '2006' and '2014'
GROUP BY persons.company -- creates totals per name
ORDER BY persons.company ;
For Zenera --> no patents ?
Code: Select all
SELECT persons.company ,
COUNT(distinct(case when appln_filing_year between 2006 and 2009 then ap.docdb_family_id end)) as "2006-2009",
COUNT(distinct(case when appln_filing_year between 2006 and 2009 and granted = 1 then ap.docdb_family_id end)) as "2006-2009_is_granted",
COUNT(distinct(case when appln_filing_year between 2010 and 2014 then ap.docdb_family_id end)) as "2010-2014",
COUNT(distinct(case when appln_filing_year between 2010 and 2014 and granted = 1 then ap.docdb_family_id end)) as "2010-2014_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 'Cambrex%' then 'Cambrex'
when psn_name like 'Zenara%'then 'Zenara' end) as "company", person_id from tls206_person WHERE (psn_name like 'Cambrex%' or psn_name like 'Zenara%') ) persons ON tls207_pers_appln.person_id = persons.person_id
WHERE appln_filing_year between '2006' and '2014'
GROUP BY persons.company -- creates totals per name
ORDER BY persons.company ;
For NeoPharm Inc (acquirer) - Insys Therapeutics Inc (target) (+ Insys Pharma ?)
Code: Select all
SELECT persons.company ,
COUNT(distinct(case when appln_filing_year between 2006 and 2009 then ap.docdb_family_id end)) as "2006-2009",
COUNT(distinct(case when appln_filing_year between 2006 and 2009 and granted = 1 then ap.docdb_family_id end)) as "2006-2009_is_granted",
COUNT(distinct(case when appln_filing_year between 2010 and 2014 then ap.docdb_family_id end)) as "2010-2014",
COUNT(distinct(case when appln_filing_year between 2010 and 2014 and granted = 1 then ap.docdb_family_id end)) as "2010-2014_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 'NeoPharm%' then 'NeoPharm'
when psn_name like 'Insys Therapeutics%'then 'Insys'
when psn_name like 'Insys Pharma%' then 'Insys' end) as "company", person_id from tls206_person
WHERE (psn_name like 'NeoPharm%' or psn_name like 'Insys Pharma%' or psn_name like 'Insys Therapeutics%') ) persons ON tls207_pers_appln.person_id = persons.person_id
WHERE appln_filing_year between '2006' and '2014'
GROUP BY persons.company -- creates totals per name
ORDER BY persons.company ;
As you can see from the above, it will be rather difficult to do this in 1 batch for 260 companies, unless you first do some cleaning & grouping in the names. In PATSTAT Online, this will be impossible. If you have PATSTAT installed on a local server, then I would create intermediate tables with cleaned & grouped applicant names.