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.