Count German applications (German applicants) in a set of IPC codes

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

carmen.calatrava
Posts: 8
Joined: Mon May 22, 2017 5:36 pm

Count German applications (German applicants) in a set of IPC codes

Post by carmen.calatrava » Wed Aug 02, 2017 1:04 pm

Dear PATSTAT community,

I am trying to replicate the results published by a German Ministry: the number of German patents applications to the EPO in the field of construction and engineering by German applicants from 2005 to 2012.

They consider the following IPC codes: B32B 9/00; B32B 13/00; B32B 17/00; B32B 18/00; B32B 19/00; B32B 21/00; C04B; C09D; E01B; E01C; E01D; E01F; E02[B-F]; E03[B-F]; E04[B-H]; E06[B-C]; E21B (without E21B40/00, E21B41/00, E21B43/00); E21D 09/00 - E21D 13/00.

As a result they obtain 5830 applications. Instead, I obtain 10442 applications with the following query:

SELECT DISTINCT COUNT(a.appln_id)
FROM tls201_appln a, tls209_appln_ipc aipc, tls207_pers_appln pa, tls206_person p
WHERE
a.appln_kind = 'A' -- Patent
AND a.appln_id < 900000000 -- exclude artificial applications
AND a.ipr_type = 'PI'
AND a.granted = 1
AND a.appln_filing_year>2004
AND a.appln_filing_year<2016
AND a.appln_id = aipc.appln_id
AND a.appln_id = pa.appln_id
AND pa.person_id = p.person_id
AND pa.applt_seq_nr > 0 -- consider only applicants
AND p.person_ctry_code = 'DE'
AND a.appln_auth = 'EP'
AND (aipc.ipc_class_symbol = 'B32B 9/00'
OR aipc.ipc_class_symbol = 'B32B 13/00'
OR aipc.ipc_class_symbol = 'B32B 17/00'
OR aipc.ipc_class_symbol = 'B32B 18/00'
OR aipc.ipc_class_symbol = 'B32B 19/00'
OR aipc.ipc_class_symbol = 'B32B 21/00'
OR aipc.ipc_class_symbol LIKE 'C04B%'
OR aipc.ipc_class_symbol LIKE 'C09D%'
OR aipc.ipc_class_symbol LIKE 'E01[B-D]%'
OR aipc.ipc_class_symbol LIKE 'E01F%'
OR aipc.ipc_class_symbol LIKE 'E02[B-F]%'
OR aipc.ipc_class_symbol LIKE 'E03[B-F]%'
OR aipc.ipc_class_symbol LIKE 'E04[B-H]%'
OR aipc.ipc_class_symbol LIKE 'E06[B-C]%'
OR aipc.ipc_class_symbol LIKE 'E21B%'
OR aipc.ipc_class_symbol BETWEEN 'E21D 09/00' AND 'E21D 13/00')
AND aipc.ipc_class_symbol NOT IN ('E21B 40/00', 'E21B 41/00', 'E21B 43/00')

The difference between the results published by the German Ministry and the query above is too large! I would appreciate your opinion on what the reason might be. Am I missing anything?

Thank you so much in advance!
Best regards,
Carmen Calatrava


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

Re: Count German applications (German applicants) in a set of IPC codes

Post by Geert Boedt » Wed Aug 02, 2017 5:15 pm

Hello Carmen,
2 short observations, you used different time frames, and you should do a count(distinct(tls201.appln.appln_id))
Something like this:

Code: Select all

SELECT COUNT(distinct(a.appln_id))
 FROM tls201_appln a join tls209_appln_ipc aipc on a.appln_id = aipc.appln_id
 join tls207_pers_appln pa on a.appln_id = pa.appln_id
 join tls206_person p on p.person_id = pa.person_id
 WHERE
 a.appln_kind = 'A' -- Patent (exclude the PCT applications that have not yet entered reg. phase.)
 AND a.appln_id < 900000000 -- exclude artificial applications
 AND a.ipr_type = 'PI'
 AND a.granted = 1
 AND a.appln_filing_year between 2005 and 2012
 AND pa.applt_seq_nr > 0 -- consider only applicants
 AND p.person_ctry_code = 'DE'
 AND a.appln_auth = 'EP'
 AND (aipc.ipc_class_symbol = 'B32B 9/00'
 OR aipc.ipc_class_symbol = 'B32B 13/00'
 OR aipc.ipc_class_symbol = 'B32B 17/00'
 OR aipc.ipc_class_symbol = 'B32B 18/00'
 OR aipc.ipc_class_symbol = 'B32B 19/00'
 OR aipc.ipc_class_symbol = 'B32B 21/00'
 OR aipc.ipc_class_symbol LIKE 'C04B%'
 OR aipc.ipc_class_symbol LIKE 'C09D%'
 OR aipc.ipc_class_symbol LIKE 'E01[B-D]%'
 OR aipc.ipc_class_symbol LIKE 'E01F%'
 OR aipc.ipc_class_symbol LIKE 'E02[B-F]%'
 OR aipc.ipc_class_symbol LIKE 'E03[B-F]%'
 OR aipc.ipc_class_symbol LIKE 'E04[B-H]%'
 OR aipc.ipc_class_symbol LIKE 'E06[B-C]%'
 OR aipc.ipc_class_symbol LIKE 'E21B%'
 OR aipc.ipc_class_symbol BETWEEN 'E21D 09/00' AND 'E21D 13/00')
 AND aipc.ipc_class_symbol NOT IN ('E21B 40/00', 'E21B 41/00', 'E21B 43/00')
That gives you 5131 applications, which is even less then the figure you quoted.
My experience is that it is extremely difficult to perfectly match figures across different sources if one does not know the exact methodology that was used to include or exclude patents.
Possible sources of differences could be : re-classifications, transfer of ownership, in/excluding PCT's -that have not yet entered reg.phase, weighing of applicants, etc...

A similar query could be run on PATSTAT Register, and even here there are slight variations.

Code: Select all

SELECT count(distinct (a.appln_id))
 FROM reg101_appln a join reg103_ipc aipc on a.id = aipc.id
 join reg107_parties part on a.id = part.id
 join [patstat2017a].dbo.[tls201_appln] tls201 on tls201.appln_id = a.appln_id
 WHERE
 a.appln_id <> 0 
 AND tls201.granted = 1
 AND year(a.appln_filing_date) between 2005 and 2012
 AND part.type = 'A' -- consider only applicants
 AND part.country = 'DE'
  AND (aipc.ipc_text like '%B32B9/00%'
 OR aipc.ipc_text like '%B32B13/00%'
 OR aipc.ipc_text like '%B32B17/00%'
 OR aipc.ipc_text like '%B32B18/00%'
 OR aipc.ipc_text like '%B32B19/00%'
 OR aipc.ipc_text like '%B32B21/00%'
 OR aipc.ipc_text LIKE '%C04B%'
 OR aipc.ipc_text LIKE '%C09D%'
 OR aipc.ipc_text LIKE '%E01[B-D]%'
 OR aipc.ipc_text LIKE '%E01F%'
 OR aipc.ipc_text LIKE '%E02[B-F]%'
 OR aipc.ipc_text LIKE '%E03[B-F]%'
 OR aipc.ipc_text LIKE '%E04[B-H]%'
 OR aipc.ipc_text LIKE '%E06[B-C]%'
 OR aipc.ipc_text LIKE '%E21B%'
 OR aipc.ipc_text LIKE '%E21D09%'
 OR aipc.ipc_text LIKE '%E21D10%'
 OR aipc.ipc_text LIKE '%E21D11%'
 OR aipc.ipc_text LIKE '%E21D12%'
 OR aipc.ipc_text LIKE '%E21D13%')
 AND (aipc.ipc_text NOT LIKE '%E21B40/00%')
 AND (aipc.ipc_text NOT LIKE '%E21B41/00%')
 AND (aipc.ipc_text NOT LIKE '%E21B43/00%')
If you really want to analyse the differences, then the best method is to take a couple of applications that are in 1 series, but not the other, and find to difference.
Here is an example: https://register.epo.org/espacenet/regv ... en&DB=REG
The original applicant was Bayer Intellectual Property GmbH (DE), and then a transfer took place to Allnex IP S.à.r.l. (CH) --> it shows in PATSTAT Register, but not in PATSTAT Biblio. I assume this patent would show up in DPMA statistics as filed by a German company, which is correct. Knowing this, one could then adapt the query and use the tls227_pers_publn table to link to the first publication (A1 or A2), and this will give a more precise picture on patents "filed" by a certain applicant (country).
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


Post Reply