215
edits
Changes
Jump to navigation
Jump to search
→Building Start and End Table
SQL steps are located at
E:\McNair\Projects\LBO\Clean\Building the Patent Start End Table.sql
/*
These are instructions for building the patentstartend table, which identifies for each patent (number) the dates at which it is granted and at which it expired.
It uses a database of all maintenance fee events and a list of the types of maintenance fee events and their effect the length of patent life
*/
--First, we need to import the table of Maintenance fee events (from copy in Z:\LBO\Clean, originally from Z:\USPTO_Consolidated\Maint_Fee_Events)
CREATE TABLE Maintfeeevents (
patentNumber VARCHAR(7) NOT NULL,
appNumber VARCHAR(8) NOT NULL,
smallEntity VARCHAR(1) NOT NULL,
appFilingDate DATE NOT NULL,
grantDate DATE NOT NULL,
feeDate DATE NOT NULL,
feeCode VARCHAR(5) NOT NULL
);
\COPY Maintfeeevents FROM 'MaintFeeEvents_20160613.txt' WITH DELIMITER AS E' ' HEADER NULL AS '' CSV;
--Now import table of fee codes (and the duration of the extensions) from (Z:\LBO\Clean)
--daysDuration represents the number of days from grantdate that patent will remain active due to maintenance fee event
--I.e., 8 years for 4th year extension, 12 years for 8th year extension, 20 years for 12th year extension
--Note that we give the expiration events a value of 0 since these can occur after any such extension (or lack thereof).
CREATE TABLE PatentExpirationRules (
feeCode VARCHAR(5) NOT NULL,
description VARCHAR(255) NOT NULL,
daysIncrement INTEGER NOT NULL,
daysDuration INTEGER NOT NULL
);
\COPY PatentExpirationRules FROM 'patentExpirationRules.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV;
--We can approximate the actual expiration date by taking the max of daysDuration and adding that number of days to the original grantdate
SELECT M.patentnumber, MAX(M.grantdate) as grantdate, MAX(GREATEST(M.grantdate + P.daysDuration, M.grantdate + 1460)) as expdate
INTO patentstartend
FROM maintfeeevents as M RIGHT JOIN patentexpirationrules as P ON M.feecode = P.feecode
GROUP BY M.patentnumber
;
/*
There is an alternative way to do this using fee expiration events; however, there are two problems:
1) The USPTO may have accidentally omitted some expiration events
2) Sometimes there are late payments; this leads to reinstatement of the patent. Should be solved by taking last expiration event, but there are potential problems:
2a) See problem 1
2b) Might not have expiration event at conclusion of 20 years (after all three extensions expire)
*/
