Historical Data

By design, the LiveData tool only maps new and updated work orders and requests. If you want older work data or request data to be mapped it requires some manual SQL queries in the database. It can take a while for the LiveData tool to get all of the historical data updated into the spatial data layers.

1. The WKSPATIALCHANGE table contains pending work data that needs to be processed into the spatial tables. To force historical data to process, a series of SQL statements can be used to insert the historical records into this table.
2. The following SQL statements can be used to process historical Requests:
   • To add historical asset locations to the spatial layers:

   INSERT INTO WKSPATIALCHANGE (SPCH_PARENTID, SPCH_LOC_ID, SPCH_LOCTYPE, SPCH_TYPE, SPCH_DATETIME,SPCH_ASSETID,SPCH_CATINV) SELECT RQ_ID, RQ_ID as ID2, 'WKREQ','INSERTUPDATE',GetDate(),RQ_INFR_ID, RQ_INV_ID From WKREQ WHERE RQ_INFR_ID IS NOT NULL AND (Insert additional WHERE here like WHERE RQ_MOD_DT > '1/1/2000' AND RQ_MOD_DT < '11/1/2008')

   • To add historical X/Y coordinates to spatial layers:

   INSERT INTO WKSPATIALCHANGE (SPCH_PARENTID, SPCH_LOC_ID, SPCH_LOCTYPE, SPCH_TYPE, SPCH_DATETIME,SPCH_X,SPCH_Y) SELECT RQ_ID, RQ_ID as ID2, 'WKREQ','INSERTUPDATE',GetDate(),RQ_X_COORD, RQ_Y_COORD From WKREQ WHERE RQ_X_COORD IS NOT NULL AND RQ_Y_COORD IS NOT NULL AND (Insert additional WHERE here)

   • To add historical Addresses to spatial layers:

   INSERT INTO WKSPATIALCHANGE (SPCH_PARENTID, SPCH_LOC_ID, SPCH_LOCTYPE, SPCH_TYPE, SPCH_DATETIME,SPCH_ADDRESS) SELECT RQ_ID, RQ_ID as ID2, 'WKREQ','INSERTUPDATE',GetDate(),IsNull(Cast(RQ_LOC_BDG as varchar),'') + ' ' + IsNull(RQ_LOC_B2,'') + ' ' + IsNull(RQ_LOC_DIR,'') + ' ' + IsNull(RQ_LOC_PT,'') + ' ' + IsNull(RQ_LOC_STR,'') + ' ' + IsNull(RQ_LOC_TY,'') + ' ' + IsNull(RQ_LOC_SFX,'') From WKREQ WHERE RQ_LOC_BDG IS NOT NULL AND (Insert additional WHERE here)

3. The following SQL statements can be used to process historical Work Orders:
   • To add historical asset locations to the spatial layers:

   INSERT INTO WKSPATIALCHANGE (SPCH_PARENTID, SPCH_LOC_ID, SPCH_LOCTYPE, SPCH_TYPE, SPCH_DATETIME,SPCH_ASSETID,SPCH_CATINV) SELECT AS_WO_ID, AS_ID , 'WKWOASSET','INSERTUPDATE',GetDate(),AS_INV_ID, AS_CAT_INV From WKWOASSET WHERE AS_INV_ID IS NOT NULL AND (Insert additional WHERE here)

   • To add historical X/Y coordinates to spatial layers:

   INSERT INTO WKSPATIALCHANGE (SPCH_PARENTID, SPCH_LOC_ID, SPCH_LOCTYPE, SPCH_TYPE, SPCH_DATETIME,SPCH_X,SPCH_Y) SELECT WO_WL_ID, WL_ID, 'WKORDERLOC','INSERTUPDATE',GetDate(),WL_X_COORD, WL_Y_COORD From WKORDERLOC WHERE WL_X_COORD IS NOT NULL AND WL_Y_COORD IS NOT NULL AND (Insert additional WHERE here)

   • To add historical Addresses to spatial layers:

   INSERT INTO WKSPATIALCHANGE (SPCH_PARENTID, SPCH_LOC_ID, SPCH_LOCTYPE, SPCH_TYPE, SPCH_DATETIME,SPCH_ADDRESS) SELECT WL_WO_ID,WL_ID, 'WKORDERLOC','INSERTUPDATE',GetDate(),IsNull(Cast(WL_ADR_BDG as varchar),'') + ' ' + IsNull(WL_ADR_B2,'') + ' ' + IsNull(WL_ADR_DIR,'') + ' ' + IsNull(WL_ADR_PT,'') + ' ' + IsNull(WL_ADR_STR,'') + ' ' + IsNull(WL_ADR_TY,'') + ' ' + IsNull(WL_ADR_SFX,'') From WKORDERLOC WHERE WL_ADR_BDG IS NOT NULL AND (Insert additional WHERE here)