gistfile1.txt

-- Make changes to the table that we created in the ESRI model builder script
-- Eden contains null values set metro empty values to null so they will match during union
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
SET RNO = NULL
WHERE RNO = ''
-- Eden contains null values set metro empty values to null so they will match during union
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
SET OWNER1 = NULL
WHERE OWNER1 = ''
-- Eden contains null values set metro empty values to null so they will match during union
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
SET OWNER2 = NULL
WHERE OWNER2 = ''
-- Eden contains null values..set metro empty values to null so they will match during union
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
SET OWNER3 = NULL
WHERE OWNER3 = ''

--UPDATE THE SITEADDR SO THAT IT HAS A FAKE STREET NUMBER AND NAME SO EDEN WILL ACCEPT IT INTO THE DATABASE
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
SET SITEADDR='123 NO SITUS'
WHERE SITEADDR='NO SITUS' OR SITEADDR=''

--UPDATE THE OWNER ADDRESS SO THAT IT HAS A FAKE STREET NUMBER AND NAME SO EDEN WILL ACCEPT IT INTO THE DATABASE
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
SET OWNERADDR='123 NO MAILING', OWNERCITY = 'WILSONVILLE', OWNERSTATE = 'OR', OWNERZIP = '97219'
where OWNERADDR = '' or OWNERADDR = 'NO MAILING ADDRESS'


--Create Temp Metro table to update the tlid to match eden apn
-- Check if temp table exists, and if it does drop it
IF OBJECT_ID('tempdb..#GG_Temp_Parcel','local') IS NOT NULL
BEGIN
DROP TABLE #GG_Temp_Parcel
Print 'table #GG_Temp_Parcel deleted'
END

-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to
-- by pass the openquery syntax
CREATE TABLE #GG_Temp_Parcel(
TLID NVARCHAR(16),
COUNTY NVARCHAR(1),
EDEN_APN NVARCHAR(29),
FirstSix nvarchar(6),
NextTwo nvarchar(2),
LastFive nvarchar(5) )

INSERT INTO #GG_Temp_Parcel(TLID, COUNTY)
SELECT TLID, COUNTY
FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
--TRIM TRAILING SPACES DUE TO EDEN USING CHAR FIELDS
UPDATE #GG_Temp_Parcel
SET TLID = RTRIM(TLID)
-- START PROCESS TO TRANSITION TLID TO EDEN APN
UPDATE #GG_Temp_Parcel
SET EDEN_APN = TLID
--
Update #GG_Temp_Parcel
Set    EDEN_APN = replace(EDEN_APN, '31W', '3S1W')
WHERE COUNTY = 'C';
Update #GG_Temp_Parcel
Set    EDEN_APN = replace(EDEN_APN, '31E', '3S1E');
Update #GG_Temp_Parcel
Set    EDEN_APN = replace(EDEN_APN, '2S1', '2S1W');
Update #GG_Temp_Parcel
Set    EDEN_APN = replace(EDEN_APN, '1S1', '1S1W');
Update #GG_Temp_Parcel
Set    EDEN_APN = replace(EDEN_APN, '3S1', '3S1W')
WHERE COUNTY = 'W';

Update #GG_Temp_Parcel
Set    EDEN_APN = replace(EDEN_APN, '  ', '__');
Update #GG_Temp_Parcel
Set    EDEN_APN = replace(EDEN_APN, ' ', '_');

Update #GG_Temp_Parcel
Set    FirstSix = SUBSTRING(EDEN_APN, 1, 6)
Update #GG_Temp_Parcel
Set    NextTwo = SUBSTRING(EDEN_APN, 7, 2)
Update #GG_Temp_Parcel
Set    LastFive = SUBSTRING(EDEN_APN, 9, 5)

Update #GG_Temp_Parcel
Set    NEXTTWO = replace(NEXTTWO, '0', '_')
WHERE NEXTTWO LIKE '%0%';

Update #GG_Temp_Parcel
Set    EDEN_APN = (FirstSix+NextTwo+LastFive)

--UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.EDEN_APN TO CONTAIN THE UPDATED EDEN STYLE PARCEL NUMBER
UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
SET sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.EDEN_APN= #GG_Temp_Parcel.EDEN_APN
FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO, #GG_Temp_Parcel
WHERE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.TLID = #GG_Temp_Parcel.TLID
--------

--Exlude from eden updates data that would not be clean
-- Check if temp table exists, and if it does drop it
IF OBJECT_ID('tempdb..#GG_Temp_Eden_Exclude','local') IS NOT NULL
BEGIN
DROP TABLE #GG_Temp_Eden_Exclude
Print 'table #GG_Temp_Eden_Exclude deleted'
END

-- Exlude from eden updates data that would not be clean
-- 
CREATE TABLE #GG_Temp_Eden_Exclude(
APN NVARCHAR(29))


--REPORT: LIST ITEMS IN EDEN FOR CLEANUP
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'	
PRINT '--------------------------------------------------------------------------------------------'		
PRINT '-----------------------THIS UPDATE FROM METRO AS THE FOLLOWING ISSUES ----------------------'
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'
PRINT 'SPLIT POLYGONS: HAVING DUPLICATE EDEN_APN(APN) AND RNO(TAX_ID)'
SELECT COUNT(*), EDEN_APN, RNO FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
GROUP BY EDEN_APN, RNO
HAVING COUNT(*) > 1
--PUT RESULTS INTO EXCLUDE FILE
--Insert into #GG_Temp_Eden_Exclude(APN)
--SELECT EDEN_APN AS APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
--GROUP BY EDEN_APN, RNO
--HAVING COUNT(*) > 1
-- END EXCLUDE ADD
PRINT 'METRO TAXLOTS HAVING A BLANK RNO(TAX_ID)'
SELECT  TLID, RNO, EDEN_APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
WHERE RNO = '' OR RNO IS NULL
PRINT 'METRO TAXLOTS HAVING A BLANK OWNER1'
SELECT  TLID, RNO, OWNER1, OWNER2, EDEN_APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
WHERE OWNER1 = '' OR OWNER1 IS NULL
--PUT RESULTS INTO EXCLUDE FILE
--Insert into #GG_Temp_Eden_Exclude(APN)
--SELECT EDEN_APN AS APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
--WHERE RNO = '' OR RNO IS NULL OR OWNER1 = '' OR OWNER1 IS NULL
--GROUP BY EDEN_APN

-- END EXCLUDE ADD
PRINT '--------------------------------------------------------------------------------------------'



--------

-- Check if temp table exists, and if it does drop it
IF OBJECT_ID('tempdb..#GG_Temp_Eden','local') IS NOT NULL
BEGIN
DROP TABLE #GG_Temp_Eden
Print 'table ggtempeden deleted'
END

-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to
-- by pass the openquery syntax
CREATE TABLE #GG_Temp_Eden(
APN NVARCHAR(29),
RNO  NVARCHAR(10),
OWNER1 NVARCHAR(30),
OWNER2 NVARCHAR(20),
JOIN_ID INT,
OWN_ID INT,
SDATE DATETIME )

-- Insert our data into temp table from EDEN
INSERT INTO #GG_Temp_Eden
SELECT P.APN AS APN, P.TAX_ID AS RNO, O.LNAME AS OWNER1, O.FNAME AS OWNER2, OJ.JOIN_ID AS JOIN_ID, OJ.OWN_ID AS OWN_ID, OJ.START_DATE AS SDATE
FROM EDENSQL.ParcelTest.dbo.ESLPARCR P
		LEFT OUTER JOIN EDENSQL.ParcelTest.dbo.ESLOWNRJ OJ
			ON P.PARC_ID = OJ.JOIN_ID
				LEFT OUTER JOIN EDENSQL.ParcelTest.dbo.ESLOWNRR O
					ON OJ.OWN_ID = O.OWN_ID
WHERE P.RETIRED_DATE IS NULL  AND OJ.REL_TYPE = 'P' 
--AND P.TAX_ID IS NOT NULL AND P.TAX_ID <> ''

--REMOVE TRAILING SPACES FROM EDEN TABLE
UPDATE #GG_Temp_Eden
SET RNO = RTRIM(RNO),
	APN = RTRIM(APN),
	OWNER1 = RTRIM(OWNER1),
	OWNER2 = RTRIM(OWNER2)

--REPORT: LIST ITEMS IN EDEN FOR CLEANUP
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'	
PRINT '--------------------------------------------------------------------------------------------'		
PRINT '--------------------------------- EDEN DATA ISSUES -----------------------------------------'
PRINT '-----------------------THIS UPDATE FROM METRO AS THE FOLLOWING ISSUES ----------------------'
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'	
PRINT 'DUPLICATE APN FROM EDEN: Constraints- These are flaged as NOT RETIRED(RETIRED_DATE IS NULL)'
SELECT * FROM #GG_Temp_Eden
WHERE APN IN (
 SELECT APN
 FROM #GG_Temp_Eden
 GROUP BY APN 
 HAVING (COUNT(APN ) > 1))
 ORDER BY APN, RNO
-- Insert APN's that we will exclude from the final table creation for the EDEN Update
--Insert into #GG_Temp_Eden_Exclude(APN)
--SELECT DISTINCT APN FROM #GG_Temp_Eden
--WHERE APN IN (
-- SELECT APN
-- FROM #GG_Temp_Eden
-- GROUP BY APN 
-- HAVING (COUNT(APN ) > 1))
--
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'
PRINT 'NULL OR BLANK TAX_ID FROM EDEN: Constraints- These are flaged as NOT RETIRED(RETIRED_DATE IS NULL)'
SELECT APN, RNO AS TAX_ID, OWNER1 AS LNAME, OWNER2 AS FNAME, JOIN_ID, OWN_ID FROM #GG_Temp_Eden
WHERE RNO IS NULL OR RNO = ''
-- Insert APN's that we will exclude from the final table creation for the EDEN Update
--Insert into #GG_Temp_Eden_Exclude(APN)
--SELECT APN FROM #GG_Temp_Eden
--WHERE RNO IS NULL OR RNO = ''
--

PRINT '--------------------------------------------------------------------------------------------'
--END REPORT: 

-- Check if temp table exists, and if it does drop it
IF OBJECT_ID('tempdb..#GG_Temp_Eden_ParcelRename','local') IS NOT NULL
BEGIN
DROP TABLE #GG_Temp_Eden_ParcelRename
Print 'table GG_Temp_Eden_ParcelRename deleted'
END

-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to
-- by pass the openquery syntax
CREATE TABLE #GG_Temp_Eden_ParcelRename(
APN NVARCHAR(29),
RNOADDRESS NVARCHAR(17)
)

INSERT INTO #GG_Temp_Eden_ParcelRename(APN, RNOADDRESS)
SELECT M.EDEN_APN AS APN, M.RNO + LEFT(M.OWNER1, 3) AS RNOADDRESS  FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] as M
WHERE Not EXISTS
(SELECT * 
    FROM EDENSQL.ParcelTest.dbo.ESLPARCR AS E
    WHERE M.EDEN_APN = E.APN)
    
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'	
PRINT '--------------------------------------------------------------------------------------------'		
PRINT '--------------------------------- EDEN DATA ISSUES -----------------------------------------'
PRINT '-----------------PARCELS IN METRO WITH NEW APN CHECK FOR RENAMES IN EDEN--------------------' 
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'
   
SELECT PR.APN AS NEW_APN, TE.APN AS OLD_APN, TE.RNO, TE.OWNER1, TE.OWNER2, TE.JOIN_ID AS PARCEL_ID FROM #GG_Temp_Eden_ParcelRename AS PR,#GG_Temp_Eden AS TE
WHERE PR.RNOADDRESS = TE.RNO + LEFT(TE.OWNER1, 3)

 
-- Insert APN's that we will exclude from the final table creation for the EDEN Update
--Insert into #GG_Temp_Eden_Exclude(APN)
--SELECT PR.APN  FROM #GG_Temp_Eden_ParcelRename AS PR,#GG_Temp_Eden AS TE
--WHERE PR.RNOADDRESS = TE.RNO + LEFT(TE.OWNER1, 3)   


  
-- Check if temp table exists, and if it does drop it
IF OBJECT_ID('sde_vector.dbo.GG_EDEN_Update_ALL') IS NOT NULL
BEGIN
DROP TABLE sde_vector.dbo.GG_EDEN_Update_ALL
Print 'Table sde_vector.dbo.GG_EDEN_Update_ALL deleted'
END

CREATE
TABLE sde_vector.dbo.GG_EDEN_Update_ALL(
APN NVARCHAR(29),
RNO NVARCHAR(10),
OWNER1 NVARCHAR(30),
OWNER2 NVARCHAR(20),
OWNERADDR NVARCHAR(35),
OWNER_ADDR NVARCHAR(35),
OWNER_APT_SUITE NVARCHAR(35),
OWNERCITY NVARCHAR(30),
OWNERSTATE NVARCHAR(2),
OWNERZIP NVARCHAR(10),
SITEADDR NVARCHAR(35),
SITECITY NVARCHAR(15),
SITESTATE NVARCHAR(2),
SITEZIP NVARCHAR(30)) 

INSERT INTO sde_vector.dbo.GG_EDEN_Update_ALL(APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITESTATE, SITEZIP)
select EDEN_APN AS APN, RNO, LEFT(OWNER1, 30), LEFT(OWNER2, 20), OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, 'OR' as SITESTATE, SITEZIP
FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] AS M
GROUP BY EDEN_APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITEZIP




UPDATE sde_vector.dbo.GG_EDEN_Update_ALL
SET RNO = RTRIM(RNO),
	APN = RTRIM(APN),
	OWNER1 = RTRIM(OWNER1),
	OWNER2 = RTRIM(OWNER2),
	OWNERADDR = RTRIM(OWNERADDR),
	OWNERCITY = RTRIM(OWNERCITY),
	OWNERSTATE = RTRIM(OWNERSTATE),
	OWNERZIP = RTRIM(OWNERZIP),
	SITEADDR = RTRIM(SITEADDR),
	SITECITY = RTRIM(SITECITY),
	SITEZIP = RTRIM(SITEZIP)


PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'	
PRINT '--------------------------------------------------------------------------------------------'		
PRINT '--------------------------------- EDEN DATA REFRESH ----------------------------------------'
PRINT '-----------------PARCELS IN METRO THAT ARE ON THE EDEN EXCLUDE LIST-------------------------' 
PRINT '--------------------------------------------------------------------------------------------'
PRINT '--------------------------------------------------------------------------------------------'
   	
select EDEN_APN AS APN, RNO, LEFT(OWNER1, 30), LEFT(OWNER2, 20), OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, 'OR' as SITESTATE, SITEZIP
FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] AS M, #GG_Temp_Eden_Exclude AS E

WHERE EXISTS
(SELECT DISTINCT E.APN 
    FROM #GG_Temp_Eden_Exclude AS E
    WHERE M.EDEN_APN = E.APN)
GROUP BY EDEN_APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITEZIP