Spanish applicant 1st Publications by tech field

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

jantonio.peces
Posts: 8
Joined: Mon Apr 18, 2016 1:22 pm

Spanish applicant 1st Publications by tech field

Post by jantonio.peces » Mon May 28, 2018 4:33 pm

Dear all,
We're trying to get the Spanish applicant 1st patent publications for some IPCs and some CPCs.
But surely I need some expert advice because of my unfrequent practice.
Please, is my query OK?
Thanks in advance.
Regards,
José A. Peces

SELECT distinct ppbn.appln_id, year(publn_date), ppbn.publn_auth, ppbn.publn_nr, ppbn.publn_kind
FROM tls211_pat_publn ppbn
JOIN tls227_pers_publn p ON ppbn.pat_publn_id= p.pat_publn_id
Join tls206_person per on p.person_id= per.person_id
JOIN tls209_appln_ipc i ON ppbn.appln_id = i.appln_id
WHERE per.person_ctry_code = 'ES' AND ppbn.publn_auth='ES' and APPLT_SEQ_NR > 0 and ppbn.publn_kind in ('A1','A2','U') and year(publn_date)>='2000'
AND ipc_class_symbol like 'F03D%'

union

SELECT distinct ppbn.appln_id, year(publn_date), ppbn.publn_auth, ppbn.publn_nr, ppbn.publn_kind
FROM tls211_pat_publn ppbn
JOIN tls227_pers_publn p ON ppbn.pat_publn_id= p.pat_publn_id
Join tls206_person per on p.person_id= per.person_id
Join tls224_appln_cpc c on ppbn.appln_id =c.appln_id
WHERE per.person_ctry_code = 'ES' AND ppbn.publn_auth='ES' and APPLT_SEQ_NR > 0 and ppbn.publn_kind in ('A1','A2','U') and year(publn_date)>='2000'
AND cpc_class_symbol like 'Y02E 10/7%'
order by year(publn_date), ppbn.appln_id asc


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

Re: Spanish applicant 1st Publications by tech field

Post by EPO / PATSTAT Support » Tue May 29, 2018 10:23 am

Hello Jantonio,
your query looks fine, apart from 1 small mistake;
'Y02E 10/7%' should have 2 spaces between the E and the 10. (But maybe double spaces are removed in forum posts by the spell check.) Just keep in mind that CPC (and IPC) codes need 4 digits and you have to replenish with blanks. (Many other patent information tools, like espacenet, make it more user friendly by removing spaces from the IPC and CPC codes, but in a strikt database such as PATSTAT, the positions need to be correct.
You mentioned that you want "first patent publications", I am not sure what you mean by that; earliest publication or first filings (meaning no earlier priorities have claimed). Both are possible.
The query below gives you first filings by forcing the application date to be the same as the earliest filing date. But you can change this to forcing the pat_publn_id to be the same as the earliest_pat_publn_id.

Code: Select all

SELECT distinct ppbn.appln_id, year(publn_date), ppbn.publn_auth, ppbn.publn_nr, ppbn.publn_kind 
FROM tls211_pat_publn ppbn
	JOIN tls227_pers_publn p ON ppbn.pat_publn_id= p.pat_publn_id
	JOIN tls206_person per on p.person_id= per.person_id
	JOIN tls209_appln_ipc i ON ppbn.appln_id = i.appln_id
	JOIN tls201_appln on ppbn.appln_id = tls201_appln.appln_id
WHERE per.person_ctry_code = 'ES' AND ppbn.publn_auth='ES' 
	AND APPLT_SEQ_NR > 0 and ppbn.publn_kind in ('A1','A2','U') and year(publn_date)>='2000'
	AND ipc_class_symbol like 'F03D%' 
	AND appln_filing_date = earliest_filing_date
--	AND ppbn.pat_publn_id = earliest_pat_publn_id
 
 union

 SELECT distinct ppbn.appln_id, year(publn_date), ppbn.publn_auth, ppbn.publn_nr, ppbn.publn_kind 
 FROM tls211_pat_publn ppbn
	JOIN tls227_pers_publn p ON ppbn.pat_publn_id= p.pat_publn_id
	JOIN tls206_person per on p.person_id= per.person_id
	JOIN tls224_appln_cpc c on ppbn.appln_id =c.appln_id
	JOIN tls201_appln on ppbn.appln_id = tls201_appln.appln_id
WHERE per.person_ctry_code = 'ES' AND ppbn.publn_auth='ES' 
	AND APPLT_SEQ_NR > 0 
	AND ppbn.publn_kind in ('A1','A2','U') 
	AND year(publn_date)>='2000'
	AND cpc_class_symbol like 'Y02E  10/7%'
	AND appln_filing_date = earliest_filing_date
--	AND ppbn.pat_publn_id = earliest_pat_publn_id
 order by year(publn_date), ppbn.appln_id asc
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


jantonio.peces
Posts: 8
Joined: Mon Apr 18, 2016 1:22 pm

Re: Spanish applicant 1st Publications by tech field

Post by jantonio.peces » Tue May 29, 2018 1:12 pm

Thanks a lot for your response.
Yes, the spelling corrector changes two blanks into only one.

First publication in Spain is A1 or A2 for patents and U for utility models.
In this case I don't mind if it has a foreign priority. I just need the applicant to reside in ES and that the National Authority is ES too.

Thanks a lot.

Jantonio.peces


jantonio.peces
Posts: 8
Joined: Mon Apr 18, 2016 1:22 pm

Re: Spanish applicant 1st Publications by tech field

Post by jantonio.peces » Mon Jun 04, 2018 9:11 am

Hi, following my previous question.

When running that query that I sent initially I get 728 results.
How can I group those results by year so I get a publication count per year?

Thanks in advance.

JA Peces


Post Reply