Count number of granted patents per year
Posted: Wed Jan 25, 2017 6:27 pm
Hi all,
my question is somehow as stated in the subject:
I want to get a panel of all granted patents (e.g. in Germany), grouped by applicant (company) and year the patent was granted (not the application date).
I was pretty sure my code was correct, but the results from current research using this data are very unpleasant. So to hedge myself I thought I give you my Code and ask if you think it is correct.
However, it might be interesting for the rest of the community to have a correct query.
Do you think this is ok? As far as I know if I only look for granted patents, the publication date is in almost every case equal to the date the patent was granted, what is very important.
A related problem which I couldn't solve either is that in some cases (hopefully not so many) there might by duplicates as the same patent might be given different appl_id's. I just hope to eliminate this at best with the distinct command.
my question is somehow as stated in the subject:
I want to get a panel of all granted patents (e.g. in Germany), grouped by applicant (company) and year the patent was granted (not the application date).
I was pretty sure my code was correct, but the results from current research using this data are very unpleasant. So to hedge myself I thought I give you my Code and ask if you think it is correct.
However, it might be interesting for the rest of the community to have a correct query.
Code: Select all
SELECT DISTINCT TLS206_PERSON.HRM_L2 , TLS206_PERSON.HRM_L2_ID, TLS201_APPLN.EARLIEST_PUBLN_DATE, TLS211_PAT_PUBLN.PUBLN_DATE, COUNT(TLS201_APPLN.APPLN_ID)
FROM TLS201_APPLN
LEFT JOIN TLS207_PERS_APPLN ON TLS201_APPLN.APPLN_ID = TLS207_PERS_APPLN.APPLN_ID
LEFT JOIN TLS206_PERSON ON TLS207_PERS_APPLN.PERSON_ID = TLS206_PERSON.PERSON_ID
LEFT JOIN TLS211_PAT_PUBLN ON TLS201_APPLN.APPLN_ID = TLS211_PAT_PUBLN.APPLN_ID
WHERE UPPER (TLS201_APPLN.EARLIEST_FILING_YEAR) >= 2000
AND TLS201_APPLN.EARLIEST_FILING_YEAR <= 2015
AND TLS211_PAT_PUBLN.PUBLN_FIRST_GRANT = 1
AND TLS207_PERS_APPLN.APPLT_SEQ_NR > 0
AND TLS206_PERSON.PERSON_CTRY_CODE = 'DE'
GROUP BY TLS206_PERSON.HRM_L2, TLS206_PERSON.HRM_L2_ID, TLS211_PAT_PUBLN.PUBLN_DATE
HAVING COUNT(TLS201_APPLN.APPLN_ID)>0;
A related problem which I couldn't solve either is that in some cases (hopefully not so many) there might by duplicates as the same patent might be given different appl_id's. I just hope to eliminate this at best with the distinct command.