Spanish applicant 1st Publications by tech field
Posted: 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
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