-- Inventory of versions 
 
select v.owner, v.name as version_name
, v.state_id
, v.creation_time version_created 
, s.closing_time last_touched
, (datediff(day, s.closing_time, getdate())) as days_since_touch,
d.depth as lineage_depth
from SDE_versions v
inner join SDE_states s
on v.state_id = s.state_id
inner join
       (
       select v.owner, v.name as name, count(*) as depth
       from SDE_state_lineages sl
       inner join SDE_states s
       on sl.lineage_name = s.lineage_name
       inner join SDE_versions v
       on s.state_id = v.state_id
       group by v.owner, v.name
       ) d
on v.name = d.name
order by days_since_touch desc