For the results I am looking for a table with one observation for each 4-digit IPC class pair, including self parings. The three columns would be the count, and each of the 4-digit IPC codes:
Class1 Class2 countvar
A01B A01B 123
A01B A01C 1
A01B A01D 5
A01C A01B 1
A01C A01C 123
Assuming that there are ~450 4-digit IPC class codes, this should result in a table with a maximum of 450^2 = 202,500 observations. In reality, this will be a very small fraction of that because most pairings will have 0 observations and will not make it into the returned table.
The code that I have created to accomplish this task is:
Code: Select all
SELECT COUNT(*) AS obs, classA.ipc_subclass_symbol, classB.ipc_subclass_symbol FROM tls212_citation as citeA INNER JOIN tls211_pat_publn as patA ON citeA.pat_publn_id=patA.pat_publn_id AND patA.publn_auth = 'DE' AND DATEPART(year,patA.publn_date)=2010 INNER JOIN tls209_appln_ipc as classA ON patA.appln_id=classA.appln_id AND classA.ipc_position='F' AND classA.ipc_subclass_symbol != 'NULL' INNER JOIN tls212_citation as citeB ON citeA.cited_pat_publn_id=citeB.cited_pat_publn_id INNER JOIN tls211_pat_publn as patB ON citeB.pat_publn_id=patB.pat_publn_id AND patB.publn_auth = 'DE' AND DATEPART(year,patB.publn_date)=2010 INNER JOIN tls209_appln_ipc as classB ON patB.appln_id=classB.appln_id AND classB.ipc_position='F' AND classB.ipc_subclass_symbol != 'NULL' AND classA.ipc_subclass_symbol = classB.ipc_subclass_symbol GROUP BY classA.ipc_subclass_symbol, classB.ipc_subclass_symbol ORDER BY classA.ipc_subclass_symbol, classB.ipc_subclass_symbol
The problem that I am having is that I get errors that the estimated cost of the query is larger than the 1,000,000 limit. Also, I'm guessing the query will take ages.
Can anyone help me tackle this problem with different or more efficient code that I can actually use? I would much rather pull the data this way from the server rather than have to download huge tables (with one obs per citation) and do the calculation on my side.... Any help is needed. I should note that I am somewhat new to SQL.
Thank you in advance!