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.
-
m.shafique
- Posts: 1
- Joined: Tue Dec 01, 2020 7:02 pm
Post
by m.shafique » Tue Dec 01, 2020 7:19 pm
Using PATSTAT Spring 2018; MySQL.
Looking for IPC classification of patents of invention of US for certain periods. It appears that a very large number of applications in the table tls201 do not have corresponding entries in the tls209 table. Is this really so or I'm not getting it right?
Sample queries:
Code: Select all
SELECT COUNT(*)
FROM tls201_appln
WHERE appln_auth='US'
AND ipr_type = 'PI'
AND appln_kind ='A'
AND appln_filing_date != '9999-12-31'
AND appln_filing_date BETWEEN "1906-01-01" AND "1915-12-31";
# Result: 383661
Code: Select all
SELECT COUNT(*)
FROM tls201_appln
WHERE appln_auth='US'
AND ipr_type = 'PI'
AND appln_kind ='A'
AND appln_filing_date != '9999-12-31'
AND appln_filing_date BETWEEN "1906-01-01" AND "1915-12-31"
AND granted='1';
# Result: 370112
Create temporary table temp1
Code: Select all
SELECT tls201_appln.appln_id
FROM tls201_appln
WHERE appln_auth='US'
AND ipr_type = 'PI'
AND appln_kind ='A'
AND appln_filing_date != '9999-12-31'
AND appln_filing_date BETWEEN "1906-01-01" AND "1915-12-31";
# Result: 383661 row(s) affected Records: 383661 Duplicates: 0 Warnings: 0
Code: Select all
SELECT COUNT(DISTINCT t1.appln_id)
FROM tls209_appln_ipc t1
INNER JOIN temp1 ON (t1.appln_id=temp1.appln_id);
# Result: 12505
-
EPO / PATSTAT Support
- Posts: 440
- Joined: Thu Feb 22, 2007 5:33 pm
-
Contact:
Post
by EPO / PATSTAT Support » Wed Dec 02, 2020 8:46 am
Hello,
your queries are correct.
I assume that the reason for missing IPC codes is that those patents simply have not been classified, so nothing wrong with the data as such. The WIPO guide gives some information on the IPC history and when it entered into force.
I don't know how "the backlog of patents" was classified, but I assume that currently, only examiners actions of citing older applications will lead to IPC classifications being assigned.
Keep in mind that the US used to have their own classifications scheme as well;
Code: Select all
SELECT appln_filing_year, count(tls201_appln.appln_id )
FROM tls201_appln
WHERE appln_auth='US'
AND ipr_type = 'PI'
AND appln_kind ='A'
AND appln_filing_date != '9999-12-31'
and tls201_appln.appln_id in (select distinct appln_id from tls223_appln_docus)
group by appln_filing_year
order by appln_filing_year
I did a quick check to see if any of those older applications were not IPC classified in 2018 (by comparing to an older PATSTAT release), and it seem that occasionally, older patents do indeed get an IPC classification code assigned. My guess is that this is due to the fact that they were indeed cited - but I did not check this in detail via the citation data.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org