Top Applicants in certain PSN sectors

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

lica.diana.m@gmail.com
Posts: 1
Joined: Thu Oct 14, 2021 11:38 am

Top Applicants in certain PSN sectors

Post by lica.diana.m@gmail.com » Thu Oct 14, 2021 1:58 pm

Dear Sir, Dear Madam,

I am PhD student at KU Leuven (Belgium) and I am currently searching the TOP 150 applicants with the EPO and the USPTO in the following sectors combined: "HOSPITAL", "NON-PROFIT", "GOV", "UNIVERSITY".

I have formulated my query as follows:

Code: Select all

SELECT TOP 150 COUNT(*) AS NumberOfApplications, doc_std_name, psn_sector
FROM tls206_person p
JOIN tls207_pers_appln pa ON p.person_id = pa.person_id
JOIN tls201_appln a ON pa.appln_id = a.appln_id
WHERE psn_sector = 'UNIVERSITY' OR psn_sector='GOV' OR psn_sector='HOSPITAL' OR psn_sector='NON-PROFIT'
AND appln_auth= 'EP' OR appln_auth = 'US'
AND pa.applt_seq_nr > 0
AND GRANTED = 'Y'
AND appln_filing_year between 2000 and 2020
AND appln_kind = 'A'
AND docdb_family_size>2
AND a.appln_id < 900000000
GROUP BY doc_std_name, person_ctry_code, psn_sector
ORDER BY NumberOfApplications DESC
However, within the retrieved data, I can see that results related to "COMPANIES" are also included in the PSN_SECTOR column. Hence, there are obviously some mistakes in my query. I suppose this is related to duplicates, but I have not figured out how to include a SELECT DISTINCT statement. As an alternative, I have tried to use the EXCEPT "COMPANY" statement, but an error message pops up.

Would it be possible to assist me with this matter?

I thank you very much and I wish you a very nice day!

Kind regards,
Diana Maria Lica


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Top Applicants in certain PSN sectors

Post by EPO / PATSTAT Support » Thu Oct 14, 2021 8:49 pm

Hello Diana,
2 observations:
a) the AND and OR operators are executed according to standard order of precedence. Meaning: A OR B AND C is executed as (A OR (B AND C )). While what you want is ((A OR B) and C).
When you have 1 attribute to be checked against a couple -or many- possible values, you can also use the SQL "IN" instead of series of ORs.
b) the sectors have to be within the domain of what those SECTOR values can be. The PATSTAT data catalog lists them, but you can easily check them by running this short query. Looking at the results, you will see that some of the sectors are rather unimportant.

Code: Select all

SELECT psn_sector, COUNT(person_id)
FROM tls206_person
group by psn_sector
order by psn_sector
And adapting your query to the above, you would get something like this: (you need to double check the PSN_SECTOR if it is really what you want, and I would also recommend to use the PSN_NAME)

Code: Select all

SELECT TOP 150 COUNT(*) AS NumberOfApplications, psn_name, psn_sector
FROM tls206_person p
JOIN tls207_pers_appln pa ON p.person_id = pa.person_id
JOIN tls201_appln a ON pa.appln_id = a.appln_id
WHERE 
psn_sector in  ('UNIVERSITY','GOV NON-PROFIT','GOV NON-PROFIT HOSPITAL', 
'GOV NON-PROFIT UNIVERSITY','HOSPITAL','COMPANY GOV NON-PROFIT UNIVERSITY')    
AND appln_auth in ('EP','US')
AND pa.applt_seq_nr > 0
AND GRANTED = 'Y'
AND appln_filing_year between 2000 and 2020
AND appln_kind = 'A'
AND docdb_family_size>2
AND a.appln_id < 900000000
GROUP BY psn_name, person_ctry_code, psn_sector
ORDER BY NumberOfApplications DESC
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply