For research work, I would like to count the number of forward citation of companies' patent per company per year. And at current stage, I would like to show all distinct PAT_PUBLN_ID based on same EARLIST_APPLN_ID by using following codes,
Code: Select all
SELECT
tls201_appln.earliest_filing_id,
distinct (case when earliest_filing_id = earliest_filing_id then pat_publn_id end)
from tls201_appln
join tls212_citation on tls201_appln.appln_id = tls212_citation .cited_appln_id
join tls211_pat_publn on tls201_appln.appln_id = tls211_pat_publn.appln_id
where applt_seq_nr > 0
and publn_date = 2000
group by earliest_filing_id
order by earliest_filing_id