Count number of granted patents per year

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

Tim Grünebaum
Posts: 18
Joined: Thu Aug 27, 2015 12:43 pm

Count number of granted patents per year

Post by Tim Grünebaum » 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.

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.
TU Dortmund


Geert Boedt
Posts: 176
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Count number of granted patents per year

Post by Geert Boedt » Wed Jan 25, 2017 7:21 pm

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
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


Tim Grünebaum
Posts: 18
Joined: Thu Aug 27, 2015 12:43 pm

Re: Count number of granted patents per year

Post by Tim Grünebaum » Thu Jan 26, 2017 11:02 am

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
TU Dortmund


Geert Boedt
Posts: 176
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: Count number of granted patents per year

Post by Geert Boedt » Thu Jan 26, 2017 2:54 pm

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
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


Tim Grünebaum
Posts: 18
Joined: Thu Aug 27, 2015 12:43 pm

Re: Count number of granted patents per year

Post by Tim Grünebaum » Thu Jan 26, 2017 5:39 pm

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.
TU Dortmund


Post Reply