1,181
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 14:55, 2 May 2017
, 14:55, 2 May 2017no edit summary
SELECT COUNT(*) FROM ptoproperty WHERE documentid LIKE 'D%';
--1128247
===US Patent Assignee Table===
Note: Table made for Julia by [[Marcela Interiano]]
Table made in the patent database using the USPTO assignment data.
The first step was to include the last_update_date in with the data from the ptoproperty table. The ptoproperty table contains only the filing date, which is not useful as we are looking for the current patent holders. The table ptoproperty_patent was used for the patent numbers as this table was cleaned to include only patent numbers, no application or publication numbers.
DROP TABLE ptoproperty_patent_update;
CREATE TABLE ptoproperty_patent_update AS
SELECT M1.reelno, M1.frameno, M1.patentno, M2.last_update_date
FROM ptoproperty_patent M1, ptotracking2 M2
WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno) AND (M1.patentno = M2.documentid);
DROP TABLE ptoproperty_patent_minupdate;
CREATE TABLE ptoproperty_patent_minupdate AS
SELECT reelno, frameno, patentno, min(last_update_date) FROM ptoproperty_patent_update GROUP BY reelno, frameno, patentno, last_update_date;
DROP TABLE ptoassignee_us_patent;
CREATE TABLE ptoassignee_us_patent AS
SELECT M1.reelno, M1.frameno, M2.name, M1.patentno, M1.last_update_date
FROM ptoproperty_patent_minupdate M1, ptoassigneend_us_cleaned M2
WHERE (M1.reelno = M2.reelno) AND (M1.frameno = M2.frameno);
==Restructure Address Information (First Stage)==
