I would like some help and advices with a query aiming to execute a fractional count of DOCDB families from CPC Y02B by country of inventors (here France) on Patstat Online.
Here what I'm doing (kind of resourcefulness but it works fine) :
Code: Select all
SELECT DISTINCT T2.earliest_filing_year, SUM(T2.CASTFRACOUNT) FROM (SELECT T.earliest_filing_year, T.docdb_family_id, COUNT(T.REPLACED) AS COUNTALL, SUM(T.SIFR) AS COUNTFR, (CAST(SUM(T.SIFR) AS DECIMAL(3,1)) / CAST(COUNT(T.REPLACED) AS DECIMAL(3,1))) AS CASTFRACOUNT FROM (SELECT DISTINCT table1.earliest_filing_year, REPLACE(table4.person_ctry_code, ' ','NULL') AS REPLACED, table2.docdb_family_id, CASE WHEN REPLACE(table4.person_ctry_code, ' ','NULL')='FR' THEN 1 ELSE 0 END SIFR FROM tls201_appln table1 INNER JOIN tls225_docdb_fam_cpc table2 ON table1.docdb_family_id=table2.docdb_family_id INNER JOIN tls207_pers_appln table3 ON table1.appln_id=table3.appln_id INNER JOIN tls206_person table4 ON table4.person_id=table3.person_id AND table2.cpc_class_symbol LIKE 'Y02B%' AND table1.earliest_filing_year >=1970 AND table1.earliest_filing_year<=2018 AND ipr_type='PI' AND granted='Y' AND REPLACE(table4.person_ctry_code, ' ','NULL') LIKE '__' GROUP BY earliest_filing_year,REPLACE(table4.person_ctry_code, ' ','NULL'), table2.docdb_family_id) T GROUP BY T.earliest_filing_year, T.docdb_family_id) T2 GROUP BY T2.earliest_filing_year ORDER BY T2.earliest_filing_year
2) Is there any other mistakes that I could miss ? SQL always give you a result and we can easily don't see a mistake.
3) I know the earliest_filing_date is quite unconventional but I would like the date as near as possible from the date of invention.
Thanks a lot for your help,