Help with forward citations

[ash_chow]

(Background: Have to analyze patent data for a paper. I have no background in SQL. Got some example queries from a paper but they are in MySQL and need to convert them to MSSQL which seems another complex procedure.)

I have been trying to get how many forward citations does a patent have till now (from, say, 1993 or publishing date- whichever is later). However, I am failing to do so. Can anyone help me with this?

To get within DOCDOB family citations I used this query:
select*
from tls201_appln
join tls209_appln_ipc ON tls201_appln.appln_id = tls209_appln_ipc.appln_id
where appln_auth='US' And earliest_filing_year>2005 and earliest_filing_year< 2014

But this only gives within family forward citations but not all forward citations.

[nico.rasters]

Here's some basics. I do not have access to PATSTAT Online myself so the following is based on a local installation (PATSTAT Oct 2014).

If patent B is citing patent A, then B is a forward citation of A.
You can find citations in table tls212_citation.
The field `pat_publn_id` is the citing patent (B), and `cited_pat_publn_id` is the cited patent (A).
However, this table lacks a few fields to make it more useful, namely: citing_appln_id, cited_appln_id, citing_docdb_family_id, cited_docdb_family_id. Probably PATSTAT Online solves this issue.

You are using tls209_appln_ipc. This table holds the IPC classes, so it has nothing to do with citations. Furthermore, since patents generally have multiple IPC classes, by linking with this table you will retrieve the same patent multiple times.

It's best to just start with one patent to see how the queries work.
A DOCDB family can have multiple patent applications.
A patent application can have multiple publications.
A publication can have multiple citations (though I'm pretty sure that the citations are usually linked to one specific publication).
Citations themselves can be repeated due to the `citn_origin`, so you will need to correct for this in order to avoid a double count.