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

Pasdeprenom
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
from
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
where
(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.

kind regards.

Manuel Jimenez


WernerDJ
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.


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

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 @ epo.org


Post Reply