Hi
I have a requirement to identify changes to a versioned feature class since a given date. The feature class has archiving enabled and I have been looking at the documentation regarding this and have come up with the following queries which should enable detection of the deleted, modified and new features:
--DELETED
select OBJECTID, GDB_FROM_DATE, GDB_TO_DATE
from MYFC_H
where
GDB_TO_DATE <> '9999-12-31' AND
GDB_TO_DATE > '2015-06-05 00:00' AND
OBJECTID not in (select OBJECTID from MYFC_H where GDB_TO_DATE = '9999-12-31')
--MODIFIED
select OBJECTID, GDB_FROM_DATE, GDB_TO_DATE
from MYFC_H
where
GDB_TO_DATE <> '9999-12-31' AND
GDB_TO_DATE > '2015-06-05 00:00' AND
OBJECTID in (select OBJECTID from MYFC_H where GDB_TO_DATE = '9999-12-31')
--NEW
select OBJECTID, GDB_FROM_DATE, GDB_TO_DATE
from MYFC_H
where
GDB_TO_DATE = '9999-12-31' AND
GDB_FROM_DATE > '2015-06-05 00:00' AND
OBJECTID not in (select OBJECTID from MYFC_H where GDB_TO_DATE <> '9999-12-31')
This appears to be working OK, based on some limited testing, but I was just wondering if anyone can verify the above or suggest a better way to do this using SQL (SQL Server) as I'm not sure the above is the most efficient SQL to achieve this.
Any input most welcome.
Regards
John
Enabling Archiving is indeed the correct way to access data on a time stamp level.
Right click on the Database Connection DEFAULT is Current, or you can give it a date.. then the connection takes place... all your featureclasses etc are actually being viewed by a View on the back end. e.g. table name OWNER would have a view OWNER_EVW.
Editing the view for example OWNER_EVW, runs the TRIGGERS to modify the 'raw' tables. 9999 dates are current and actual dates are of those dates.
I use this technique for identifying changes of parcels within a time period. You can then also implement the time scaling in maps...etc etc.
Downfall to this is of course, no data is ever deleted. DB size grows.