Application id or family id for analysis

[Jyoti]

Dear support team,

I am collecting data for green transport innovations landscape anslysis. i have considered following parameters (one example):

Code: Select all

SELECT DISTINCT a.appln_id,a.appln_nr, a.appln_auth, a.appln_kind, a.earliest_filing_year, t.appln_title, STRING_AGG (cast (cpc_class_symbol as varchar(max)) , '; ') CPC, a.nb_applicants, a.nb_inventors, pa.invt_seq_nr, pa.applt_seq_nr,p.psn_name, p.psn_sector, p.person_ctry_code, a.appln_filing_year, a.earliest_publn_year, a.granted, a.ipr_type
FROM tls201_appln a
join tls207_pers_appln pa on a.appln_id = pa.appln_id
join tls206_person p on pa.person_id = p.person_id
left join tls202_appln_title t on a.appln_id = t.appln_id
join tls224_appln_cpc cpc on a.appln_id = cpc.appln_id
where (a.appln_id in (select appln_id from tls224_appln_cpc where left(cpc_class_symbol,8) = 'Y02T  10'))
and a.earliest_filing_year BETWEEN 1981 and 1990
group by a.appln_id,a.appln_nr, a.appln_auth, a.appln_kind, a.earliest_filing_year, t.appln_title,a.nb_applicants, a.nb_inventors, pa.invt_seq_nr, pa.applt_seq_nr, p.person_name,p.psn_name, p.psn_sector, p.person_ctry_code, a.appln_filing_year, a.earliest_publn_year, a.granted, a.ipr_type
order by a.earliest_filing_year, a.appln_id, a.nb_applicants;

and i got confused if i should consider the family id if there is repetition in the application id and tired using this"

Code: Select all

SELECT DISTINCT a.docdb_family_id, a.docdb_family_size,a.appln_nr, a.appln_auth, a.appln_kind, a.earliest_filing_year, t.appln_title, STRING_AGG (cast (cpc_class_symbol as varchar(max)) , '; ') CPC, a.nb_applicants, a.nb_inventors, pa.invt_seq_nr, pa.applt_seq_nr,p.psn_name, p.psn_sector, p.person_ctry_code, a.appln_filing_year, a.earliest_publn_year, a.granted, a.ipr_type
FROM tls201_appln a
join tls207_pers_appln pa on a.appln_id = pa.appln_id
join tls206_person p on pa.person_id = p.person_id
left join tls202_appln_title t on a.appln_id = t.appln_id
join tls224_appln_cpc cpc on a.appln_id = cpc.appln_id
where (a.appln_id in (select appln_id from tls224_appln_cpc where left(cpc_class_symbol,8) = 'Y02T 10'))
and a.earliest_filing_year BETWEEN 1981 and 1990
group by a.docdb_family_id, a.docdb_family_size, a.appln_id ,a.appln_nr, a.appln_auth, a.appln_kind, a.earliest_filing_year, t.appln_title,a.nb_applicants, a.nb_inventors, pa.invt_seq_nr, pa.applt_seq_nr, p.person_name,p.psn_name, p.psn_sector, p.person_ctry_code, a.appln_filing_year, a.earliest_publn_year, a.granted, a.ipr_type
order by a.earliest_filing_year, a.docdb_family_id, a.nb_applicants;

To my surprise i got the same number of rows(119632) when switching from application id to family id but the information like titles etc are different. Now i am confused more, which way should i do?
Please suggest.

one for query, can i assume that considering early filling year is similar to considering first priority application?

please help

Best regards
Jyoti
PhD student