WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
--9613927
The document ids in the PTO assignment data had not yet been verified as matching to the main patent table in our database (psql patent). The document ids in the PTO assignment data are stored as character strings whereas the patents in the patent table are stored as integers. Unlike the ptoprpoperty_cleaned table, all patent numbers in the patent table are unique.
The following two tables were made in order to verify that the documentids in the ptoproperty_cleaned table match to the patent table.
DROP TABLE edcheck;
CREATE TABLE edcheck AS
SELECT CAST (documentid AS INT) FROM ptotracking2;
SELECT COUNT(DISTINCT documentid) FROM edcheck;
--2343765
DROP TABLE edcheck2;
CREATE TABLE edcheck2 AS
SELECT M1.documentid, M2.patent
FROM edcheck M1, patent M2
WHERE (M1.documentid = M2.patent);
--2238305
Based on 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===