citations, how to...

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

URL260
Posts: 2
Joined: Mon Feb 03, 2014 7:49 pm

citations, how to...

Post by URL260 » Wed Feb 05, 2014 4:40 pm

Hello,
I'm a student and i have very limited skills in Patstat and mysql queries.
I'm writing my final paper and i need this informations for every patent (filed between 1995 and 2012) by a certain
-description of the patent (title, filing year, publication year)
-number of years the company has paid the fees
-number of citations of the patents received by the applicant itself
-number of citations received by a third-party

I think i figured out a way to obtain almost all the informations (you're free to suggest me a different way,or correct me) but i still miss the citations.

I hope that someone can help me out, so i can start to write down my paper.

this is the query:

SELECT han_id,hrm_l2,appln_title,publn_auth,publn_nr,appln_filing_year,publn_earliest_year,MAX(l520ep), granted--,prs_code
FROM tls201_appln a
JOIN tls207_pers_appln b ON a.appln_id = b.appln_id
JOIN tls202_appln_title t ON a.appln_id =t.appln_id
JOIN tls206_person c ON b.person_id = c.person_id
JOIN tls211_pat_publn d ON a.appln_id = d.appln_id /*per la data di pubblicazione*/
JOIN tls221_inpadoc_prs i ON i.appln_id = d.appln_id /*Tab. da cui estrarre le FEES*/
--JOIN tls212_citation e on a.appln_id = e.cited_appln_id
WHERE
( hrm_l2_id='6318438'
OR hrm_l2_id='6318440'
OR hrm_l2_id='6318439'
OR hrm_l2_id='6318442'
)

AND appln_auth='EP'
AND publn_auth='EP'
AND applt_seq_nr > 0 /*discriminatore che tiene in considerazione solo gli applicants e non gli inventori*/
AND appln_filing_year BETWEEN '1995' AND '2012' --AND granted = 1 /*Per eliminare le applications non risultanti in brevetto*/
GROUP BY a.appln_id
ORDER BY hrm_l2_id,appln_filing_year, publn_nr


Thank you in advance!
Marco


Post Reply