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
Spanish applicant 1st Publications by tech field
-
- Posts: 8
- Joined: Mon Apr 18, 2016 1:22 pm
-
- Posts: 425
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Spanish applicant 1st Publications by tech field
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.
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
EPO - Vienna
patstat @ epo.org
-
- Posts: 8
- Joined: Mon Apr 18, 2016 1:22 pm
Re: Spanish applicant 1st Publications by tech field
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
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
-
- Posts: 8
- Joined: Mon Apr 18, 2016 1:22 pm
Re: Spanish applicant 1st Publications by tech field
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
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