Get Information on All Patent Applications (Including Provisional Applications)

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

zecon
Posts: 4
Joined: Mon May 23, 2022 10:41 pm

Get Information on All Patent Applications (Including Provisional Applications)

Post by zecon » Mon May 23, 2022 11:01 pm

Hi all,

I’m currently trying to create a table/dataset in MySQL Workbench of all pharmaceutical patents, including those that have the provisional patent designation (USA). Essentially, the process involves first finding necessary data for all pharmaceutical applications, then locating all the provisional applications and linking them to patents that cite each provisional application as a priority (if applicable).

I found this reference that seemed useful and I tried to follow the logic in my own code, but it was also slightly confusing (I’m a newbie at this) and seems to contain a lot of superfluous information: patent-citation-backward-citation-7788.

A snippet of my code is below, but I’m not sure that this does what I want it to. Any help in this would be much appreciated.

Code: Select all

select *
from (
-- tls201
select *
from tls201_appln as a
) as app
inner join (
-- tls209_appln_ipc
select distinct (i.appln_id)
from tls209_appln_ipc as i 
where i.IPC_CLASS_SYMBOL like "A61K%"
) as ip on ip.APPLN_ID = app.APPLN_ID
inner join tls207_pers_appln as pa on ip.APPLN_ID = pa.appln_id
inner join
(	select p.PERSON_ID, p.person_ctry_code
	from tls206_person as p
    -- where p.person_ctry_code <> ""
) as pe
on (pa.person_id = pe.person_id)

inner join
(	select p.PERSON_ID, p.psn_sector,p.esc_psn_sector_rank, p.person_ctry_code
	from tls206_person as p
    -- where p.person_ctry_code <> ""
) as pe
on (pa.person_id = pe.person_id)

inner join 
(	select citing.pat_publn_id, citing.appln_id
	from tls211_pat_publn
) as citing
ON citing.appln_id = ip.appln_id

inner join 
(	select j.pat_publn_id, j.cited_appln_id
	from tls212_citation as j
) as ja
on (citing.pat_publn_id = ja.pat_publn_id)

inner join 
( select cited.pat_publn_id, cited.appln_id
	from tls211_pat_publn 
)	as cited 
on ja.cited_pat_publn_id = cited.pat_publn_id

left join 
(	select j.cited_pat_publn_id, j.cited_appln_id
	from tls212_citation as j
) as ja2
on ja2.cited_appln_id = app.appln_id
where (app.appln_kind = 'P')


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Get Information on All Patent Applications (Including Provisional Applications)

Post by EPO / PATSTAT Support » Tue May 24, 2022 2:04 pm

Hello Zecon,
you will need to recheck your code.
p.esc_psn_sector_rank is not a PATSTAT attribute and you have defined 2 aliases using the name PE.

Also: Provisional patent applications are not published since they are not examined and they are only pending at the U.S. Patent Office for 12-months. So you will not find those patents in PATSTAT. (I am not aware whether other databases provide info on such filings.)
Patent databases always (with a small exception for withdrawn priority filings) need to have a "real" publication to refer to the patents. The bibliographical data on a the published patent application is used to become the core of most patent data bases.

There is a major difference between a citation and a priority. A priority filing establishes a filing date which will be used by the examiners to establish the "cut-of" date to select relevant prior art. (In simple words: only earlier publications will be considered as possibly novelty destroying publications.) Citations are the documents cited by the examiners, but those are not the priority filings of that application. 2 different concepts.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


zecon
Posts: 4
Joined: Mon May 23, 2022 10:41 pm

Re: Get Information on All Patent Applications (Including Provisional Applications)

Post by zecon » Tue May 24, 2022 2:38 pm

Hi,

Sorry that my code might've been a tad confusing. the psn_sector_rank is a custom column made from a different query. Please ignore that piece (as well as the pe alias).

So, what I think you are saying is that I won't be able to link patent applications to provisional applications using backward or forward citations on their publications? And that provisional patent applications do not exist in the PATSTAT database? Perhaps I am misunderstanding the meaning of "provisional" (by provisional, I mean those applications such that appln_kind = 'P', and linking later patent applications that cite these applications).


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Get Information on All Patent Applications (Including Provisional Applications)

Post by EPO / PATSTAT Support » Tue May 24, 2022 3:44 pm

Hi Zecon,
I should have been more precise in my explanation.
Very limited bibliographical data on provisional applications is available in PATSTAT because they have been used as a priority filing to establish a priority date. But they were not published. So there is hardly bibliographical data except for a filing date. (an even the US register does not provide a copy).
If you run the query below you will see that all those "P" applications have an appln_id > 900000000. This means that the EPO has created those (dummy)applications to guarantee data base integrity. (see PATSTAT data catalog)

Code: Select all

SELECT*
FROM tls201_appln as appl
WHERE appln_filing_year BETWEEN 2012 AND 2020 
AND appln_auth like  'US'
and appln_kind = 'P'
--and appln_id < 900000000
ORDER BY appl.appln_filing_year
All (99.9 %) of those provisional applications have been used as a priority filing. That relationship is established via the table tls204 (see example below).

Code: Select all

SELECT priority.appln_id,priority.appln_auth, priority.appln_nr, 
priority.appln_kind, priority.appln_filing_date,
later_filing.appln_id,later_filing.appln_auth, later_filing.appln_nr, 
later_filing.appln_kind, later_filing.appln_filing_date
FROM tls201_appln as priority
join tls204_appln_prior on priority.appln_id = tls204_appln_prior.prior_appln_id
join tls201_appln later_filing on tls204_appln_prior.appln_id = later_filing.appln_id
WHERE priority.appln_filing_year = 2012
AND priority.appln_auth like  'US'
and priority.appln_kind = 'P'
ORDER BY priority.appln_id desc
Forward (or backward) citations relations are established via the table tls212. But that has no relation to the priority filings (be it provisional or not).
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


zecon
Posts: 4
Joined: Mon May 23, 2022 10:41 pm

Re: Get Information on All Patent Applications (Including Provisional Applications)

Post by zecon » Tue May 24, 2022 5:51 pm

I think your second table is getting at what I wanted to see. I confused the concept of applications "claiming priority" and applications "citing publications/other applications"; thank you for the clarification about publications and provisional applications.

From my understanding, your second block of code does the following. First, it retrieves data on all provisional applications from tls201. Second, it joins these applications using data from tls204 on applications that have been claimed as priority. Third, it intersects the applications that have been claimed as priority with later-filed applications that claim them as priority. Is this a correct understanding of that code? If so, then this is exactly the mechanism I wanted to get at.


Post Reply