Page 1 of 1

Count number of granted patents per year

Posted: Wed Jan 25, 2017 6:27 pm
by Tim Grünebaum
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.

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; 
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.

Re: Count number of granted patents per year

Posted: Wed Jan 25, 2017 7:21 pm
by Geert Boedt
Hello Tim,
can you give me an overview of the "columns" you would like to have in your final list; is it a "count" of the applications, or a list of applications, which you then will count afterwards.
Having a count over a grouped pair of publication dates is probably not what you want.
Geert

Re: Count number of granted patents per year

Posted: Thu Jan 26, 2017 11:02 am
by Tim Grünebaum
Hello Geert,

what I would like to have in the end is the following:

HRM_L2 | HRM_L2_ID | PUBLN_DATE | COUNT(TLS201_APPLN.APPLN_ID)
...
BAYER | 1721979 | 01.01.2005 | 1
BAYER | 1721979 | 04.01.2005 | 3
BAYER | 1721979 | 11.01.2005 | 9
BAYER | 1721979 | 12.01.2005 | 3
BAYER | 1721979 | 15.01.2005 | 1
BAYER | 1721979 | 18.01.2005 | 4
BAYER | 1721979 | 19.01.2005 | 2
BAYER | 1721979 | 25.01.2005 | 4
BAYER | 1721979 | 26.01.2005 | 1
BAYER | 1721979 | 28.01.2005 | 1
BAYER | 1721979 | 31.01.2005 | 1
BAYER | 1721979 | 01.02.2005 | 3
BAYER | 1721979 | 02.02.2005 | 1
BAYER | 1721979 | 08.02.2005 | 6
BAYER | 1721979 | 09.02.2005 | 1
BAYER | 1721979 | 15.02.2005 | 1
BAYER | 1721979 | 16.02.2005 | 1
BAYER | 1721979 | 21.02.2005 | 3
...

This is what my code actually gives (excluding EARLIEST_FILING_YEAR).
So I only want a count of granted applications with the date the patent was granted.
In a next step I can sum up all grants of a company in one year using other programs, because Patstat does only give the publication date and not the year.
So the code seems ok to me but I am not sure of this really gives all granted patents with date of grant.

Regards

Tim

Re: Count number of granted patents per year

Posted: Thu Jan 26, 2017 2:54 pm
by Geert Boedt
Hello Tim,
you can group by the year of grant by using the SQL function 'YEAR'.
If you want to count only the granted applications, it might be better to use the applicant name that was effectively linked to the publication of the grant (the patent specification). This is done by linking the records for publications of grants (identified through PUBLN_FIRST_GRANT =1 in TLS211) to the TLS206_PERSON table via the TLS227_PERS_PUBLN table. The differences are not that much, but it gives a better representation of the patent owner at the moment of grant. I have adapted the query to the latest PATSTAT Online release with the changes of attribute names from EEE_PPAT to PSN.
Also keep in mind that this query gives about 450.000 rows because you do a count across all the patent publishing authorities. I assume you will want to narrow this down via the PUBLN_AUTH by for example adding PUBLN_AUTH = 'EP' (or something similar...)

Code: Select all

SELECT TLS206_PERSON.psn_name , year(TLS211_PAT_PUBLN.PUBLN_DATE) grant_date, COUNT(TLS211_PAT_PUBLN.APPLN_ID) granted_#
FROM TLS206_PERSON join tls227_pers_publn on tls206_person.person_id = tls227_pers_publn.person_id
join TLS211_PAT_PUBLN ON TLS211_PAT_PUBLN.PAT_PUBLN_ID =  tls227_pers_publn.pat_publn_id
WHERE year(TLS211_PAT_PUBLN.PUBLN_DATE)  between 2000 and 2015
AND TLS211_PAT_PUBLN.PUBLN_FIRST_GRANT = 1
AND tls227_PERS_PUBLN.APPLT_SEQ_NR > 0
AND TLS206_PERSON.PERSON_CTRY_CODE = 'DE'
GROUP BY TLS206_PERSON.psn_name , year(TLS211_PAT_PUBLN.PUBLN_DATE)
order by TLS206_PERSON.psn_name , year(TLS211_PAT_PUBLN.PUBLN_DATE) asc

Re: Count number of granted patents per year

Posted: Thu Jan 26, 2017 5:39 pm
by Tim Grünebaum
Hello Geert,

thanks for your code. Some smaller remarks:
- The YEAR operator is very useful! However it doesn't change the count per year.
- It seems the code is quite the same as mine with minor changes. E.g. does it matter to exclude the DISTINCT command in the first line? I think I better keep it to not double count patents.
- The only reason to group by PERSON_NAME instead of the harmonized HRM_L2 is to better distinguish applicants, right? This is true indeed, but I am looking forward to merge the data with industry data. So if e.g. John Smith works at Volkswagen, then PERSON_NAME will give me "John Smith" and HRM_L2 will give me "Volkswagen". Linking TLS211_PAT_PUBLN and TLS227_PERS_PUBLN is only an improvement in performance, right? If not I do not understand the point :|
- The publication date is equal to the date of grant in our case, right? However that seems pretty random to me...
If it is of any interest, I am working with the autumn 2015 version.