1,181
edits
Changes
Jump to navigation
Jump to search
Patent Assignment Data Restructure (view source)
Revision as of 17:40, 2 May 2017
, 17:40, 2 May 2017→Data Cleanup Progress
SELECT COUNT(*) FROM (SELECT DISTINCT patentno FROM ptoassignee_us_patent) AS P;
--2345763
===Final Table (name TBD)===
CREATE TABLE patpub AS
(SELECT ptoproperty_patent.reelno, ptoproperty_patent.frameno, ptoproperty_patent.invention_title,
ptoproperty_patent.patentno, ptoproperty_pub.pubno FROM ptoproperty_patent LEFT JOIN ptoproperty_pub
ON
(ptoproperty_patent.reelno = ptoproperty_pub.reelno) AND (ptoproperty_patent.frameno =
ptoproperty_pub.frameno)
AND (ptoproperty_patent.invention_title = ptoproperty_pub.invention_title)
UNION
SELECT ptoproperty_pub.reelno, ptoproperty_pub.frameno, ptoproperty_pub.invention_title,
ptoproperty_patent.patentno, ptoproperty_pub.pubno FROM ptoproperty_patent RIGHT JOIN ptoproperty_pub
ON
(ptoproperty_patent.reelno = ptoproperty_pub.reelno) AND (ptoproperty_patent.frameno =
ptoproperty_pub.frameno)
AND (ptoproperty_patent.invention_title = ptoproperty_pub.invention_title));
CREATE TABLE patapp AS
(SELECT ptoproperty_patent.reelno, ptoproperty_patent.frameno, ptoproperty_patent.invention_title,
ptoproperty_patent.patentno, ptoproperty_app.appno FROM ptoproperty_patent LEFT JOIN ptoproperty_app
ON
(ptoproperty_patent.reelno = ptoproperty_app.reelno) AND (ptoproperty_patent.frameno =
ptoproperty_app.frameno)
AND (ptoproperty_patent.invention_title = ptoproperty_app.invention_title)
UNION
SELECT ptoproperty_app.reelno, ptoproperty_app.frameno, ptoproperty_app.invention_title,
ptoproperty_patent.patentno, ptoproperty_app.appno FROM ptoproperty_patent RIGHT JOIN ptoproperty_app
ON
(ptoproperty_patent.reelno = ptoproperty_app.reelno) AND (ptoproperty_patent.frameno =
ptoproperty_app.frameno)
AND (ptoproperty_patent.invention_title = ptoproperty_app.invention_title));
==Restructure Address Information (First Stage)==
