Hello Mike,
there are a couple of flaws in the approach: the min(a2.appln_id) does not guarantee that you have the first application of the family, you are doing an aggregation using publications and if I understood correctly, you want to have a "family - level" final table.
The main challenge is to aggregate data which is stored at application and publication level to family level. Keeping in mind that 1 family can have many members (with different application dates) and each application different publication (dates), it becomes difficult to keep track of what conditions need to be applied to get good data to create your final table. So I changed it a bit around and made a start table that gives for each family a sorted list of all family members and their application date. As we are only interested in the earliest application, I need to have a way to identify the earliest one. To do this, I used ROW NUMBER over PARTITION which I can use as a selection criteria.
Via the WHERE clause I made the conditions a bit more general to : each family has at least 1 German inventor, each family has at least 1 application that has a 'Y02C 10' , 'Y02C 20' or 'Y02C 30' classification.
This was done to avoid missing out on applications because a country code or classification is missing in the earliest application of the family. If you agree with that, this is the query to make that table:
Code: Select all
SELECT a.docdb_family_id, a.appln_id, a.appln_filing_date
,ROW_NUMBER() OVER(partition by a.docdb_family_id ORDER BY a.docdb_family_id ASC,
a.appln_filing_date,a.appln_id ) AS Row
FROM tls201_appln a
where
docdb_family_id in (select distinct docdb_family_id from tls201_appln join tls207_pers_appln
on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
where invt_seq_nr > 0 and person_ctry_code = 'DE')
--at least 1 German inventor in one of the family members
AND docdb_family_id in (select distinct 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, 8) in ('Y02C 10','Y02C 20','Y02C 30'))
--at least 1 application having one of the CPC codes
group by a.docdb_family_id
,a.appln_id
,a.appln_filing_date
order by docdb_family_id asc, row asc
If one would be working on a local PATSTAT installation, one could store the above result in a temporary table or view to do the next step. This is not possible in PATSTAT Online, so one needs to use the above query as a sub query to create the final table. (Or you could export it into EXCEL and take it from there.) Using the same query as above in sub query to do the counts, no forgetting to add criteria to only take the first "ROW" and limiting via the application filing year:
Code: Select all
Select
FORMAT(temp.appln_filing_date,'yyyy-MM') yyyy_mm,
--DATEPART(month, temp.appln_filing_date) as month,
--year(temp.appln_filing_date) as year,
count(distinct temp.docdb_family_id)as NumberOfPatentFamilies
from
(SELECT a.docdb_family_id, a.appln_id, a.appln_filing_date
,ROW_NUMBER() OVER(partition by a.docdb_family_id ORDER BY a.docdb_family_id ASC,
a.appln_filing_date,a.appln_id ) AS Row
FROM tls201_appln a
where
docdb_family_id in (select distinct docdb_family_id from tls201_appln join tls207_pers_appln
on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
where invt_seq_nr > 0 and person_ctry_code = 'DE')
--at least 1 German inventor in one of the family members
AND docdb_family_id in (select distinct 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, 8) in ('Y02C 10','Y02C 20','Y02C 30'))
--at least 1 application having one of the CPC codes
group by a.docdb_family_id
,a.appln_id
,a.appln_filing_date) temp
where temp.Row = 1 and year(temp.appln_filing_date) between 1999 and 2018
group by FORMAT(temp.appln_filing_date,'yyyy-MM')
--DATEPART(month, temp.appln_filing_date), year(temp.appln_filing_date)
order by yyyy_mm
--year, month
Another approach is to simply extract the data into a MS ACCESS where you have the full freedom to create extra intermediate tables (and graphs).
Code: Select all
select distinct appln_id from tls224_appln_cpc
where left(cpc_class_symbol, 8) in ('Y02C 10','Y02C 20','Y02C 30')
Take the option GLOBAL, and then include the DOCDB family members. This will give you the worldwide selection which would allow you to start comparing DE inventor data with other countries.