1,181
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 15:14, 8 May 2017
, 15:14, 8 May 2017→Data Cleanup Progress
--2238305
DROP TABLE edcheck; CREATE TABLE edcheck AS SELECT DISTINCT documentid FROM ptotracking2; --2343765 DROP TABLE edcheck2; CREATE TABLE edcheck2 AS SELECT CAST(documentid AS INT)FROM edcheck; --2343765 DROP TABLE edcheck3; CREATE TABLE edcheck3 AS SELECT M1.documentid, M2.patent FROM edcheck2 M1, patent M2 WHERE M1.documentid = M2.patent; --2238305 Based on the iterations of these tables, we could conclude that our original patent data forms the majority of the patents undergoing reassignments or transactions.
===US ONLY Patent Assignee Table===
SELECT COUNT(*) FROM (SELECT DISTINCT patentno FROM ptoassignee_us_patent) AS P;
--2345763
===Current Assignee using Recorded Date===
Each assignment has three dates: filingdate, recorded_date, last_update_date. The filingdate corresponds to the filing of the assignment with the USPTO. The recorded_date is the date the transaction was recorded. The last_update_date is the date the USPTO verifies that the assignment still holds. In the ptoassignee_us_patent table, the last_update_date is used to find the current assignee.
Prior to Sonia's work with the ptoassignment table address data, the table ptoassignee_current was made using the most recent recorded_date. This method though is flawed given that additional transactions could have current previously that are still in effect as patents can have multiple assignees. These codes can be used for constructing similar tables using the address data Sonia has cleaned in the following sections of this project.
To begin with, the ptoproperty_patent table was cleaned to drop all duplicates. Then the table was matched with the assignee table.
DROP TABLE ptoassigneev1;
CREATE TABLE ptoassigneev1 AS
SELECT M1.reelno, M1.frameno, M1.documentid, M1.country, M1.filingdate, M2.last_update_date,
M2.recorded_date
FROM ptoproperty_patent2 M1, ptoassignmentnd M2
WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
DROP TABLE ptoassigneev2;
CREATE TABLE ptoassigneev2 AS
SELECT M1.reelno, M1.frameno, M1.documentid, M2.name, M1.country, M1.last_update_date, M1.recorded_date,
M2.addrline1, M2.addrline2, M2.city, M2.state, M2.postcode
FROM ptoassigneev1 M1, ptoassigneend M2
WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
--9634942
DROP TABLE datecheck;
CREATE TABLE datecheck AS
SELECT documentid, max(recorded_date) as recorded_date FROM ptoassignee_patent GROUP BY documentid;
--2343765
DROP TABLE datecheck;
CREATE TABLE datecheck AS
SELECT documentid, max(recorded_date) as recorded_date FROM ptoassigneev2 GROUP BY documentid;
--4374885
DROP TABLE ptoassignee_current;
CREATE TABLE ptoassignee_current AS
SELECT M1.reelno, M1.frameno, M2.documentid, M1.name, M1.last_update_date, M2.recorded_date, M1.addrline1,
M1.addrline2, M1.country, M1.city, M1.state, M1.postcode
FROM ptoassigneev2 M1, datecheck M2
WHERE (M1.documentid = M2.documentid) AND (M1.recorded_date = M2.recorded_date);
--5004038
ADD IN THE HISTPATENT STUFF FOR THE APPNUM
===Final Table (name TBD)===
