Forward citation.

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

Posts: 5
Joined: Mon Apr 02, 2018 9:20 am

Forward citation.

Post by Pasdeprenom » Mon Apr 02, 2018 9:32 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:
select a.docdb_family_id,
count(distinct(j.appln_id)) as forward
ee a
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
and datediff(u.EARLIEST_FILING_date, un.EARLIEST_FILING_date)<=1095
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.

kind regards.

Manuel Jimenez

Posts: 1
Joined: Tue Aug 21, 2018 7:41 am

Re: Forward citation.

Post by WernerDJ » Tue Aug 21, 2018 7:48 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.

Posts: 231
Joined: Thu Feb 22, 2007 5:33 pm

Re: Forward citation.

Post by EPO / PATSTAT Support » Tue Aug 21, 2018 10:02 am

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 @

Post Reply