thanks for your help in advance!
I'd like to retrieve on
"Which are the top 10 most cited applications in related with (CPC: E04B 1/762) filed between 2000-2005, 2006-2010,2010-2015 ? "
I finished to retrieve in one particular timescale 2000-2005 as follows:
Code: Select all
SELECT DISTINCT TOP 10 docdb_family_id,nb_citing_docdb_fam,a.appln_id,CONCAT(appln_auth, appln_nr, appln_kind) AS country_id, appln_filing_date,earliest_filing_id,earliest_filing_date
FROM tls201_appln a
JOIN tls224_appln_cpc c ON a.appln_id=c.appln_id
WHERE cpc_class_symbol= 'E04B 1/762'
AND appln_filing_year <='2005'
AND appln_filing_year >='2000'
AND a.appln_id=earliest_filing_id
ORDER BY nb_citing_docdb_fam DESC, docdb_family_id DESC
but I'm just wondering how can I retrieve the three time scale at the same statement ( to get the top 10 at each 3 time scale, at one same retrieval), I tried the UNION ALL in this way:
Code: Select all
SELECT * FROM (
SELECT TOP 10 docdb_family_id,nb_citing_docdb_fam,a.appln_id,CONCAT(appln_auth, appln_nr, appln_kind) AS country_id, appln_filing_date,earliest_filing_id,earliest_filing_date
FROM tls201_appln a
JOIN tls224_appln_cpc c ON a.appln_id=c.appln_id
WHERE cpc_class_symbol= 'E04B 1/762'
AND appln_filing_year <='2005'
AND appln_filing_year >='2000'
AND a.appln_id=earliest_filing_id
ORDER BY nb_citing_docdb_fam DESC, docdb_family_id DESC) d
UNION ALL
SELECT * FROM (
SELECT TOP 10 docdb_family_id,nb_citing_docdb_fam,a.appln_id,CONCAT(appln_auth, appln_nr, appln_kind) AS country_id, appln_filing_date,earliest_filing_id,earliest_filing_date
FROM tls201_appln a
JOIN tls224_appln_cpc c ON a.appln_id=c.appln_id
WHERE cpc_class_symbol= 'E04B 1/762'
AND appln_filing_year <='2010'
AND appln_filing_year >='2005'
AND a.appln_id=earliest_filing_id
ORDER BY nb_citing_docdb_fam DESC, docdb_family_id DESC) e
Any suggestion on this? Thanks!
And I after the retrieval I still need to retrieve in about 100 different CPC symbols, how can I batch on that at one same retrieval?
Thank you so much !
HZ