Identifying changes using Geodatabase Archive Tables

[JohnFannon]

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

[MichaelRobb]

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.