query about patents applications and granted in a NUT area and a year.

[ccobcas]

Hello,
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

[EPO / PATSTAT Support]

Hello ccobcas,
your query is correct; to see only the granted patents you will need to un-comment the " a.granted= 'Y' " condition - which you probably know-.
The fact that you also see (some) inventors is because those inventors are also applicants. So when a person is as well inventor as applicant, then invt_seq_nr as well as applt_seq_nr will be > 0. Run the query below and you will see that for the very first row in the result list. (On PATSTAT 2022a and 2022b).
You could exclude those cases by specifying the condition "applt_seq_nr > 0 and invt_seq_nr = 0". But you have to keep in mind that all patent application that only have applicants who at the same time are inventor will be excluded from your results. Your list will look more -but not exclusive- as a "list of companies".

Code: Select all

SELECT a.appln_id,a.appln_filing_year,a.appln_filing_date, b.applt_seq_nr,
b.invt_seq_nr, 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 applt_seq_nr > 0 --and invt_seq_nr = 0
--AND a.granted='Y' 
AND (p.publn_kind= 'A1' or p.publn_kind= 'A2')
AND a.appln_auth = 'EP' or a.appln_auth = 'ES'
AND b.applt_seq_nr>0
AND a.appln_filing_date  between '2010-01-01' and '2021-12-31'
ORDER BY a.appln_id, appln_filing_year, b.applt_seq_nr

I hope this answers your question.

[ccobcas]

I´ve just nitice that this query doesn´t filter by "appln_filing_date" but works ok with the "appln_filing_year" field!!!! why is this happening???

Thanks for everything!!

[EPO / PATSTAT Support]

There was a small mistake in the query, the WHERE clause specified

Code: Select all

AND a.appln_filing_date  >= '2010-01-01'
AND a.appln_filing_date  <= '2021-12-31'

as well as

Code: Select all

AND a.appln_filing_year= '2010'

, and as a result only the year 2010 was taken.
I corrected the above query and removed the limitation of the 2010 year.