SQL view to display the last reconcile/post dates in SDE tables

317
3
06-20-2018 12:32 PM
Highlighted
New Contributor II

Hi, I would like to create a view that would show the dates for the last reconcile and post operation for each version in GDB. I understand each state has a closing date and each version can have last touched data. However I am having a difficult time figuring out at which point either the states or lineages would be considered "reconciled/posted". I was trying to use a combination of SDE_states, SDE_versions, SDE_lineages_modified and SDE_state_lineages.

Would anyone have SQL code or ideas how to accomplish this?

The idea here is to create a report that would list which versions need to be reconciled and posted within a certain time period, to hold our users accountable.

Thank you!

Jan

3 Replies
Highlighted
New Contributor

I would also appreciate an example of how to construct a sql query returning a list of versions with last modified dates.   

Reply
0 Kudos
Highlighted
New Contributor

Jan-

After I viewed the relational diagram, you could use the following query to obtain the last time a versions was modified, which includes edit and rec/post operations.

SELECT versions.name, states.closing_time
from versions
inner join states on versions.state_id = states.state_id;

http://desktop.arcgis.com/en/arcmap/10.3/manage-data/gdbs-in-sql-server/pdf/sdesystables_diagram.pdf

Reply
0 Kudos
Highlighted
New Contributor II

Hi, thanks for providing me with the query. This query is helpful to find out when the version was last touched, however this would still not provide the date when the last reconcile/post operation was run. Is there some special flag indicating which state/lineage had reconcile/post run on it specifically? Thanks for your help!

Reply
0 Kudos