count applications using patent family

[Maly]

Hey,
Can you tell me please how to count granted applications of a country (Germany for example) using patent family.
Thanks

[Geert Boedt]

Helo Maly,

The concept of the simple patent family is to group applications for the same invention that have been filed I different countries. Therefore if you count patent families -via a count(distinct tls201_appln.docdb_family_id) - instead of applications for 1 specific country, you will hardly see any differences in the numbers. The only differences will be for families containing multiple applications for the SAME country, which is rather exceptional.
This query will count per applications filing year all the granted applications, having a smart grid Y04S classification. The count is done at family level.

Code: Select all

SELECT appln_filing_year , count(distinct tls201_appln.docdb_family_id) from 
tls201_appln join tls224_appln_cpc on tls201_appln.appln_id = tls224_appln_cpc.appln_id
where left(cpc_class_symbol,4) = 'Y04S' and appln_auth = 'DE' and granted = 1 and tls201_appln.appln_id < 900000000 and tls201_appln.appln_kind = 'A'
group by appln_filing_year
order by appln_filing_year

With the above query, you have to keep in mind that you only count the applications filed in DE, you will not count the patents filed at the EPO, which have been granted and validated in DE. (A granted EP application validated in Germany is not re-published in Germany.)
If you want to include those applications, you have to add them explicetely. The query below does that:

Code: Select all

SELECT  appln_filing_year , count(distinct tls201_appln.docdb_family_id) 
from 
tls201_appln join tls224_appln_cpc on tls201_appln.appln_id = tls224_appln_cpc.appln_id
left join tls221_inpadoc_prs on tls201_appln.appln_id = tls221_inpadoc_prs.appln_id 
where left(cpc_class_symbol,4) = 'Y04S' and (appln_auth = 'GB' or (appln_auth = 'EP' and prs_code = 'pgfp'  and l501ep = 'GB')) and granted = 1 and tls201_appln.appln_id < 900000000 and tls201_appln.appln_kind = 'A' 
group by appln_filing_year
order by appln_filing_year

[Maly]

Thank you for your helpful answer. Is it ok if i use earliest_filing_year instead of appln_filing_year to consider only priority filings.
thanks

[Geert Boedt]

Helo Maly,
Using the earliest_filing_year will give you a unique "first date" which is also valid at the docdb_family_id level. (at least in most cases, there are families with 100's of members, where there are deviations)
But keep in mind that using the earliest_filing_year instead of appln_filing_year will take the earliest year for each application (or docdb_family), but it does not force to only take priority filings. One also needs to keep in mind that priority filings are not necessary first filings; they can in turn claim an earlier priority themselves. And most first filings are not priority filings neither.

In order to only consider first filings you need to be sure that the filing does not have any priority filing. This can be done by for example adding in the WHERE clause the condition that appln_id should not occur in the table TLS204_APPLN-PRIOR which links applications with their priorities. If an application does not claim a priority then it will not have a matching record. Example using the previous code:

Code: Select all

SELECT  appln_filing_year , count(distinct tls201_appln.docdb_family_id) 
from 
tls201_appln join tls224_appln_cpc on tls201_appln.appln_id = tls224_appln_cpc.appln_id
left join tls221_inpadoc_prs on tls201_appln.appln_id = tls221_inpadoc_prs.appln_id 
where left(cpc_class_symbol,4) = 'Y04S' and (appln_auth = 'GB' or (appln_auth = 'EP' and prs_code = 'pgfp'  and l501ep = 'GB')) and granted = 1 and tls201_appln.appln_id < 900000000 and tls201_appln.appln_kind = 'A' 
and tls201_appln.appln_id not in (select distinct appln_id from tls204_appln_prior)
group by appln_filing_year
order by appln_filing_year

The results are drastically reduced because more then 90 % of EP applications DO claim a priority.