I need a bit of help with a complicated (for me atleast) SQL query.
The goal is to find all patents in a given time frame from a selected number of countires, that are citing at least three patents. However these three (or more) cited patens need to belong to a different Section/ Classes or Subclasses. I'm not sure which granularity I can make the most use of.
Example:
For Sections: Patent xxxxxxx is in Section A and is quoting three patents, which are cetegorised (first position only) in respectively Sections A, D and F
For Classes: Patent xxxxxxx is in A23 and is quoting three patents, which are cetegorised (first position only) in respectively A18, A09 and F12
etc.
There are two lines where I'm struggling. Line two (mainly) and I'm not sure about line 21. Both are commented in the query below.
Here my query so far (and I needed help with that too):
Code: Select all
select citing_pub.publn_nr, cited_pub.publn_nr, more2.number_bw_citation
from -- !!!This is where I need a count function for the cited publication and distinction between the IPC Sections/Classes!!!
tls209_appln_ipc as citing_ipc
join tls201_appln as citing_ap on citing_ipc.appln_id = citing_ap.appln_id
join tls211_pat_publn as citing_pub on citing_ap.appln_id = citing_pub.appln_id
join tls212_citation on citing_pub.pat_publn_id = tls212_citation.pat_publn_id
join tls211_pat_publn as cited_pub on tls212_citation.cited_pat_publn_id = cited_pub.pat_publn_id
join tls201_appln as cited_ap on cited_pub.appln_id = cited_ap.appln_id
join tls209_appln_ipc as cited_ipc on cited_ap.appln_id = cited_ipc.appln_id
join
(Select citing_publication.appln_id, count (cited_publication.appln_id) number_bw_citation
from
tls211_pat_publn cited_publication
join tls212_citation as citation on cited_publication.pat_publn_id = citation.pat_publn_id
join tls211_pat_publn as citing_publication on citation.pat_publn_id = citing_publication.pat_publn_id
where citing_publication.appln_id in ('145086','258027','217882')
group by citing_publication.appln_id
having count(cited_publication.appln_id)>2) as more2 on citing_ap.appln_id = more2.appln_id -- Is this line correct? I'm a bit confused about this one.
where citing_pub.publn_date BETWEEN '1991-01-01' AND '2016-07-31'
and cited_ap. appln_kind = 'A'
AND citing_ap.appln_auth IN ('AL','AT','BA','BE','BG','CH','CS','CY','CZ','DE','DK','EE','ES','FI','FR','GB','GE','GR','HR','HU','IE','IS','IT','LI','LT','LU','LV','MC','MD','ME','MK','MT','NL','NO','PL','PT','RO','RS','RU','SE','SI','SK','SM','TR','UA','YU','EP')
AND citing_ap.granted =1
order by citing_ap.appln_id
I'm using a local (University) PATSTAT database. Version 2016b (thus the end date end of July)
Kind regards
Angel