EPO - Discussion forums

Posted: **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

Posted: **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.

Posted: **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.)

EPO - Discussion forums

Forward citation.

Forward citation.

Re: Forward citation.

Re: Forward citation.