I am interested in listing the patent applications and patents granted between years 2010 and 2021, for an area in spain that i have identify with nuts “ES11” . I have made a query but i checked the results are not ok. Also i can´t find a field to filter by applicant and not by inventor appart from “tls207_pers_appln.applt_seq_nr>0” and this doesn´t seem to work!!!!
Thank you very much for your help.
This is my query:
Code: Select all
SELECT a.appln_id,a.appln_filing_year,a.appln_filing_date, c.person_name, c.person_address, N.nuts, N.nuts_level, N.nuts_label, p.publn_nr,p.publn_date, p.publn_kind
FROM tls201_appln a
JOIN tls211_pat_publn p on p.appln_id=a.appln_id
JOIN tls207_pers_appln b on a.appln_id=b.appln_id
JOIN tls206_person c on c.person_id=b.person_id
JOIN tls904_nuts N on N.nuts = c.nuts and c.nuts like 'ES11%' and N.nuts_level <=3
WHERE c.person_ctry_code = 'ES'
--AND a.granted='Y'
AND (p.publn_kind= 'A1' or p.publn_kind= 'A2')
AND a.appln_filing_year= '2010'
AND a.appln_auth = 'EP' or a.appln_auth = 'ES'
AND b.applt_seq_nr>0
AND a.appln_filing_date >= '2010-01-01'
AND a.appln_filing_date <= '2021-12-31'
ORDER BY a.appln_filing_year