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

[JanTeisinger]

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

[chrisSchmuck]

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

[chrisSchmuck]

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

[JanTeisinger]

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!

[ashizzle]

SELECT edit_moment FROM sde.sde_branch_tables_modified where branch_id = 0 order by edit_moment desc limit 1

0 = default.  see the rest of branches in sde_branches.