· 8 years ago · Aug 09, 2018, 01:24 PM
1-- Make changes to the table that we created in the ESRI model builder script
2-- Eden contains null values set metro empty values to null so they will match during union
3UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
4SET RNO = NULL
5WHERE RNO = ''
6-- Eden contains null values set metro empty values to null so they will match during union
7UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
8SET OWNER1 = NULL
9WHERE OWNER1 = ''
10-- Eden contains null values set metro empty values to null so they will match during union
11UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
12SET OWNER2 = NULL
13WHERE OWNER2 = ''
14-- Eden contains null values..set metro empty values to null so they will match during union
15UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
16SET OWNER3 = NULL
17WHERE OWNER3 = ''
18
19--UPDATE THE SITEADDR SO THAT IT HAS A FAKE STREET NUMBER AND NAME SO EDEN WILL ACCEPT IT INTO THE DATABASE
20UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
21SET SITEADDR='123 NO SITUS'
22WHERE SITEADDR='NO SITUS' OR SITEADDR=''
23
24--UPDATE THE OWNER ADDRESS SO THAT IT HAS A FAKE STREET NUMBER AND NAME SO EDEN WILL ACCEPT IT INTO THE DATABASE
25UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
26SET OWNERADDR='123 NO MAILING', OWNERCITY = 'WILSONVILLE', OWNERSTATE = 'OR', OWNERZIP = '97219'
27where OWNERADDR = '' or OWNERADDR = 'NO MAILING ADDRESS'
28
29
30--Create Temp Metro table to update the tlid to match eden apn
31-- Check if temp table exists, and if it does drop it
32IF OBJECT_ID('tempdb..#GG_Temp_Parcel','local') IS NOT NULL
33BEGIN
34DROP TABLE #GG_Temp_Parcel
35Print 'table #GG_Temp_Parcel deleted'
36END
37
38-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to
39-- by pass the openquery syntax
40CREATE TABLE #GG_Temp_Parcel(
41TLID NVARCHAR(16),
42COUNTY NVARCHAR(1),
43EDEN_APN NVARCHAR(29),
44FirstSix nvarchar(6),
45NextTwo nvarchar(2),
46LastFive nvarchar(5) )
47
48INSERT INTO #GG_Temp_Parcel(TLID, COUNTY)
49SELECT TLID, COUNTY
50FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
51--TRIM TRAILING SPACES DUE TO EDEN USING CHAR FIELDS
52UPDATE #GG_Temp_Parcel
53SET TLID = RTRIM(TLID)
54-- START PROCESS TO TRANSITION TLID TO EDEN APN
55UPDATE #GG_Temp_Parcel
56SET EDEN_APN = TLID
57--
58Update #GG_Temp_Parcel
59Set EDEN_APN = replace(EDEN_APN, '31W', '3S1W')
60WHERE COUNTY = 'C';
61Update #GG_Temp_Parcel
62Set EDEN_APN = replace(EDEN_APN, '31E', '3S1E');
63Update #GG_Temp_Parcel
64Set EDEN_APN = replace(EDEN_APN, '2S1', '2S1W');
65Update #GG_Temp_Parcel
66Set EDEN_APN = replace(EDEN_APN, '1S1', '1S1W');
67Update #GG_Temp_Parcel
68Set EDEN_APN = replace(EDEN_APN, '3S1', '3S1W')
69WHERE COUNTY = 'W';
70
71Update #GG_Temp_Parcel
72Set EDEN_APN = replace(EDEN_APN, ' ', '__');
73Update #GG_Temp_Parcel
74Set EDEN_APN = replace(EDEN_APN, ' ', '_');
75
76Update #GG_Temp_Parcel
77Set FirstSix = SUBSTRING(EDEN_APN, 1, 6)
78Update #GG_Temp_Parcel
79Set NextTwo = SUBSTRING(EDEN_APN, 7, 2)
80Update #GG_Temp_Parcel
81Set LastFive = SUBSTRING(EDEN_APN, 9, 5)
82
83Update #GG_Temp_Parcel
84Set NEXTTWO = replace(NEXTTWO, '0', '_')
85WHERE NEXTTWO LIKE '%0%';
86
87Update #GG_Temp_Parcel
88Set EDEN_APN = (FirstSix+NextTwo+LastFive)
89
90--UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.EDEN_APN TO CONTAIN THE UPDATED EDEN STYLE PARCEL NUMBER
91UPDATE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
92SET sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.EDEN_APN= #GG_Temp_Parcel.EDEN_APN
93FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO, #GG_Temp_Parcel
94WHERE sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO.TLID = #GG_Temp_Parcel.TLID
95--------
96
97--Exlude from eden updates data that would not be clean
98-- Check if temp table exists, and if it does drop it
99IF OBJECT_ID('tempdb..#GG_Temp_Eden_Exclude','local') IS NOT NULL
100BEGIN
101DROP TABLE #GG_Temp_Eden_Exclude
102Print 'table #GG_Temp_Eden_Exclude deleted'
103END
104
105-- Exlude from eden updates data that would not be clean
106--
107CREATE TABLE #GG_Temp_Eden_Exclude(
108APN NVARCHAR(29))
109
110
111--REPORT: LIST ITEMS IN EDEN FOR CLEANUP
112PRINT '--------------------------------------------------------------------------------------------'
113PRINT '--------------------------------------------------------------------------------------------'
114PRINT '--------------------------------------------------------------------------------------------'
115PRINT '-----------------------THIS UPDATE FROM METRO AS THE FOLLOWING ISSUES ----------------------'
116PRINT '--------------------------------------------------------------------------------------------'
117PRINT '--------------------------------------------------------------------------------------------'
118PRINT '--------------------------------------------------------------------------------------------'
119PRINT 'SPLIT POLYGONS: HAVING DUPLICATE EDEN_APN(APN) AND RNO(TAX_ID)'
120SELECT COUNT(*), EDEN_APN, RNO FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
121GROUP BY EDEN_APN, RNO
122HAVING COUNT(*) > 1
123--PUT RESULTS INTO EXCLUDE FILE
124--Insert into #GG_Temp_Eden_Exclude(APN)
125--SELECT EDEN_APN AS APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
126--GROUP BY EDEN_APN, RNO
127--HAVING COUNT(*) > 1
128-- END EXCLUDE ADD
129PRINT 'METRO TAXLOTS HAVING A BLANK RNO(TAX_ID)'
130SELECT TLID, RNO, EDEN_APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
131WHERE RNO = '' OR RNO IS NULL
132PRINT 'METRO TAXLOTS HAVING A BLANK OWNER1'
133SELECT TLID, RNO, OWNER1, OWNER2, EDEN_APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
134WHERE OWNER1 = '' OR OWNER1 IS NULL
135--PUT RESULTS INTO EXCLUDE FILE
136--Insert into #GG_Temp_Eden_Exclude(APN)
137--SELECT EDEN_APN AS APN FROM sde_vector.cowgis.GG_TEMP_TAXLOTS_METRO
138--WHERE RNO = '' OR RNO IS NULL OR OWNER1 = '' OR OWNER1 IS NULL
139--GROUP BY EDEN_APN
140
141-- END EXCLUDE ADD
142PRINT '--------------------------------------------------------------------------------------------'
143
144
145
146--------
147
148-- Check if temp table exists, and if it does drop it
149IF OBJECT_ID('tempdb..#GG_Temp_Eden','local') IS NOT NULL
150BEGIN
151DROP TABLE #GG_Temp_Eden
152Print 'table ggtempeden deleted'
153END
154
155-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to
156-- by pass the openquery syntax
157CREATE TABLE #GG_Temp_Eden(
158APN NVARCHAR(29),
159RNO NVARCHAR(10),
160OWNER1 NVARCHAR(30),
161OWNER2 NVARCHAR(20),
162JOIN_ID INT,
163OWN_ID INT,
164SDATE DATETIME )
165
166-- Insert our data into temp table from EDEN
167INSERT INTO #GG_Temp_Eden
168SELECT 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
169FROM EDENSQL.ParcelTest.dbo.ESLPARCR P
170 LEFT OUTER JOIN EDENSQL.ParcelTest.dbo.ESLOWNRJ OJ
171 ON P.PARC_ID = OJ.JOIN_ID
172 LEFT OUTER JOIN EDENSQL.ParcelTest.dbo.ESLOWNRR O
173 ON OJ.OWN_ID = O.OWN_ID
174WHERE P.RETIRED_DATE IS NULL AND OJ.REL_TYPE = 'P'
175--AND P.TAX_ID IS NOT NULL AND P.TAX_ID <> ''
176
177--REMOVE TRAILING SPACES FROM EDEN TABLE
178UPDATE #GG_Temp_Eden
179SET RNO = RTRIM(RNO),
180 APN = RTRIM(APN),
181 OWNER1 = RTRIM(OWNER1),
182 OWNER2 = RTRIM(OWNER2)
183
184--REPORT: LIST ITEMS IN EDEN FOR CLEANUP
185PRINT '--------------------------------------------------------------------------------------------'
186PRINT '--------------------------------------------------------------------------------------------'
187PRINT '--------------------------------------------------------------------------------------------'
188PRINT '--------------------------------- EDEN DATA ISSUES -----------------------------------------'
189PRINT '-----------------------THIS UPDATE FROM METRO AS THE FOLLOWING ISSUES ----------------------'
190PRINT '--------------------------------------------------------------------------------------------'
191PRINT '--------------------------------------------------------------------------------------------'
192PRINT '--------------------------------------------------------------------------------------------'
193PRINT 'DUPLICATE APN FROM EDEN: Constraints- These are flaged as NOT RETIRED(RETIRED_DATE IS NULL)'
194SELECT * FROM #GG_Temp_Eden
195WHERE APN IN (
196 SELECT APN
197 FROM #GG_Temp_Eden
198 GROUP BY APN
199 HAVING (COUNT(APN ) > 1))
200 ORDER BY APN, RNO
201-- Insert APN's that we will exclude from the final table creation for the EDEN Update
202--Insert into #GG_Temp_Eden_Exclude(APN)
203--SELECT DISTINCT APN FROM #GG_Temp_Eden
204--WHERE APN IN (
205-- SELECT APN
206-- FROM #GG_Temp_Eden
207-- GROUP BY APN
208-- HAVING (COUNT(APN ) > 1))
209--
210PRINT '--------------------------------------------------------------------------------------------'
211PRINT '--------------------------------------------------------------------------------------------'
212PRINT 'NULL OR BLANK TAX_ID FROM EDEN: Constraints- These are flaged as NOT RETIRED(RETIRED_DATE IS NULL)'
213SELECT APN, RNO AS TAX_ID, OWNER1 AS LNAME, OWNER2 AS FNAME, JOIN_ID, OWN_ID FROM #GG_Temp_Eden
214WHERE RNO IS NULL OR RNO = ''
215-- Insert APN's that we will exclude from the final table creation for the EDEN Update
216--Insert into #GG_Temp_Eden_Exclude(APN)
217--SELECT APN FROM #GG_Temp_Eden
218--WHERE RNO IS NULL OR RNO = ''
219--
220
221PRINT '--------------------------------------------------------------------------------------------'
222--END REPORT:
223
224-- Check if temp table exists, and if it does drop it
225IF OBJECT_ID('tempdb..#GG_Temp_Eden_ParcelRename','local') IS NOT NULL
226BEGIN
227DROP TABLE #GG_Temp_Eden_ParcelRename
228Print 'table GG_Temp_Eden_ParcelRename deleted'
229END
230
231-- Create the temp table from the EDEN database, we will then use this temp table for future queries since it will allow us to
232-- by pass the openquery syntax
233CREATE TABLE #GG_Temp_Eden_ParcelRename(
234APN NVARCHAR(29),
235RNOADDRESS NVARCHAR(17)
236)
237
238INSERT INTO #GG_Temp_Eden_ParcelRename(APN, RNOADDRESS)
239SELECT M.EDEN_APN AS APN, M.RNO + LEFT(M.OWNER1, 3) AS RNOADDRESS FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] as M
240WHERE Not EXISTS
241(SELECT *
242 FROM EDENSQL.ParcelTest.dbo.ESLPARCR AS E
243 WHERE M.EDEN_APN = E.APN)
244
245PRINT '--------------------------------------------------------------------------------------------'
246PRINT '--------------------------------------------------------------------------------------------'
247PRINT '--------------------------------------------------------------------------------------------'
248PRINT '--------------------------------- EDEN DATA ISSUES -----------------------------------------'
249PRINT '-----------------PARCELS IN METRO WITH NEW APN CHECK FOR RENAMES IN EDEN--------------------'
250PRINT '--------------------------------------------------------------------------------------------'
251PRINT '--------------------------------------------------------------------------------------------'
252PRINT '--------------------------------------------------------------------------------------------'
253
254SELECT 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
255WHERE PR.RNOADDRESS = TE.RNO + LEFT(TE.OWNER1, 3)
256
257
258-- Insert APN's that we will exclude from the final table creation for the EDEN Update
259--Insert into #GG_Temp_Eden_Exclude(APN)
260--SELECT PR.APN FROM #GG_Temp_Eden_ParcelRename AS PR,#GG_Temp_Eden AS TE
261--WHERE PR.RNOADDRESS = TE.RNO + LEFT(TE.OWNER1, 3)
262
263
264
265-- Check if temp table exists, and if it does drop it
266IF OBJECT_ID('sde_vector.dbo.GG_EDEN_Update_ALL') IS NOT NULL
267BEGIN
268DROP TABLE sde_vector.dbo.GG_EDEN_Update_ALL
269Print 'Table sde_vector.dbo.GG_EDEN_Update_ALL deleted'
270END
271
272CREATE
273TABLE sde_vector.dbo.GG_EDEN_Update_ALL(
274APN NVARCHAR(29),
275RNO NVARCHAR(10),
276OWNER1 NVARCHAR(30),
277OWNER2 NVARCHAR(20),
278OWNERADDR NVARCHAR(35),
279OWNER_ADDR NVARCHAR(35),
280OWNER_APT_SUITE NVARCHAR(35),
281OWNERCITY NVARCHAR(30),
282OWNERSTATE NVARCHAR(2),
283OWNERZIP NVARCHAR(10),
284SITEADDR NVARCHAR(35),
285SITECITY NVARCHAR(15),
286SITESTATE NVARCHAR(2),
287SITEZIP NVARCHAR(30))
288
289INSERT INTO sde_vector.dbo.GG_EDEN_Update_ALL(APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITESTATE, SITEZIP)
290select EDEN_APN AS APN, RNO, LEFT(OWNER1, 30), LEFT(OWNER2, 20), OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, 'OR' as SITESTATE, SITEZIP
291FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] AS M
292GROUP BY EDEN_APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITEZIP
293
294
295
296
297UPDATE sde_vector.dbo.GG_EDEN_Update_ALL
298SET RNO = RTRIM(RNO),
299 APN = RTRIM(APN),
300 OWNER1 = RTRIM(OWNER1),
301 OWNER2 = RTRIM(OWNER2),
302 OWNERADDR = RTRIM(OWNERADDR),
303 OWNERCITY = RTRIM(OWNERCITY),
304 OWNERSTATE = RTRIM(OWNERSTATE),
305 OWNERZIP = RTRIM(OWNERZIP),
306 SITEADDR = RTRIM(SITEADDR),
307 SITECITY = RTRIM(SITECITY),
308 SITEZIP = RTRIM(SITEZIP)
309
310
311PRINT '--------------------------------------------------------------------------------------------'
312PRINT '--------------------------------------------------------------------------------------------'
313PRINT '--------------------------------------------------------------------------------------------'
314PRINT '--------------------------------- EDEN DATA REFRESH ----------------------------------------'
315PRINT '-----------------PARCELS IN METRO THAT ARE ON THE EDEN EXCLUDE LIST-------------------------'
316PRINT '--------------------------------------------------------------------------------------------'
317PRINT '--------------------------------------------------------------------------------------------'
318
319select EDEN_APN AS APN, RNO, LEFT(OWNER1, 30), LEFT(OWNER2, 20), OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, 'OR' as SITESTATE, SITEZIP
320FROM [sde_vector].[cowgis].[GG_TEMP_TAXLOTS_METRO] AS M, #GG_Temp_Eden_Exclude AS E
321
322WHERE EXISTS
323(SELECT DISTINCT E.APN
324 FROM #GG_Temp_Eden_Exclude AS E
325 WHERE M.EDEN_APN = E.APN)
326GROUP BY EDEN_APN, RNO, OWNER1, OWNER2, OWNERADDR, OWNERCITY, OWNERSTATE, OWNERZIP, SITEADDR, SITECITY, SITEZIP