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.
- Posts: 5
- Joined: Mon Apr 02, 2018 9:20 am
Hello, good morning,
my name is Manuel and I am trying to count number of forward citation for a list of families that I have in a table called 'ee'. I am using a 3 years time windows. My query is:
count(distinct(j.appln_id)) as forward
left join tls201_appln u on a.appln_id=u.appln_id
left join tls211_pat_publn h on a.appln_id=h.appln_id
left join tls212_citation i on h.pat_publn_id=i.cited_pat_publn_id
left join tls211_pat_publn j on i.pat_publn_id=j.pat_publn_id
left join tls201_appln un on j.appln_id=un.APPLN_ID
(PAT_CITN_SEQ_NR != 0
OR CITED_APPLN_ID != 0)
and datediff(u.EARLIEST_FILING_date, un.EARLIEST_FILING_date)<=1095
group by DOCDB_FAMILY_ID
The problem is that with a 'limit 1' the query can be running 1 day as minimum. I do not know where is the error in the code, please could you help me with this.
thank you in advance.
- Posts: 1
- Joined: Tue Aug 21, 2018 7:41 am
The datediff function requires three parameters, the first being the unit of time that you want to compare. Since you stated a difference of about one thousand I am sure not to miss the point if I guess that what you want to compare are days, the code then looks like this
and datediff(day, u.EARLIEST_FILING_date, un.EARLIEST_FILING_date)<=1095
And that works fine in Patstat.
EPO / PATSTAT Support
- Posts: 231
- Joined: Thu Feb 22, 2007 5:33 pm
Your group by should use the same ALIAS as in the select: GROUP BY a.docdb_family_id .
And if you want to do a typical "family-family" count then you should use the family_id in your count. (But maybe you want to effectively count applications, depends on your analysis.)
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org