EPO - Discussion forums

Code: Select all

SELECT DISTINCT a.appln_id, a.appln_auth, a.appln_nr, a.appln_kind, a.earliest_filing_date, b.cpc_class_symbol, c.invt_seq_nr, d.person_name, d.person_ctry_code
FROM tls201_appln a JOIN tls224_appln_cpc b ON a.appln_id = b.appln_id
LEFT OUTER JOIN tls207_pers_appln c on a.appln_id = c.appln_id
LEFT OUTER JOIN tls206_person d ON c.person_id = d.person_id
WHERE b.cpc_class_symbol = 'Y02E  10/7%' AND c.invt_seq_nr > 0
ORDER BY a.appln_auth, a.earliest_filing_date, a.appln_id, c.invt_seq_nr, d.person_ctry_code

Code: Select all

SELECT DISTINCT a.appln_id, a.appln_auth, a.appln_nr, a.appln_kind, a.earliest_filing_date, 
STRING_AGG (cast( b.cpc_class_symbol as NVARCHAR(MAX)), '; ') cpc_GROUP,
 c.invt_seq_nr, d.person_name, d.person_ctry_code
FROM tls201_appln a JOIN tls224_appln_cpc b ON a.appln_id = b.appln_id
LEFT OUTER JOIN tls207_pers_appln c on a.appln_id = c.appln_id
LEFT OUTER JOIN tls206_person d ON c.person_id = d.person_id
WHERE left (b.cpc_class_symbol, 10) =  'Y02E  10/7' AND c.invt_seq_nr > 0
and appln_filing_year between 1999 and 2018 
group by a.appln_id, a.appln_auth, a.appln_nr, a.appln_kind, a.earliest_filing_date, 
 c.invt_seq_nr, d.person_name, d.person_ctry_code
ORDER BY a.appln_auth, a.earliest_filing_date,  a.appln_id, c.invt_seq_nr, d.person_ctry_code

Code: Select all

SELECT d.person_ctry_code, SUM(tot_in_ctry/tot_in_patent) AS fractional_count
FROM 
         (SELECT 
                      t.appln_id, 
                      isnull (t1.person_ctry_code, ' ') AS person_ctry_code,
                      isnull (t1.tot_in_ctry, 1) AS tot_in_ctry,
                      isnull (t2.tot_in_patent, 1) AS tot_in_patent
                      FROM 
                      our_sample t                   
                 LEFT OUTER JOIN
           (SELECT
                     c.appln_id,
                     d.person_ctry_code,
                     COUNT (d.person_id) AS tot_in_ctry
            FROM
                      tls207_pers_appln c
                      INNER JOIN tls206_person d ON c.person_id = d.person_id 
           WHERE 
                     c.invt_seq_nr > 0
           GROUP BY c.appln_id, person_ctry_code
           ) t1 ON t.appln_id = t1.appln_id 
                     LEFT OUTER JOIN
           (SELECT
                     appln_id, MAX(invt_seq_nr) AS tot_in_patent
            FROM 
                    tls207_pers_appln
            GROUP BY appln_id HAVING MAX(invt_seq_nr) > 0
            )  t2 ON t.appln_id = t2.appln_id
            ) our_sample_with_country        
GROUP BY person_ctry_code
ORDER BY SUM(tot_in_ctry/tot_in_patent) DESC;