EffortQueries

This topic explains how to query for estimating across Agility instances.

Effort Trend

            declare @startDate datetime, @days int
            select @startDate='2009-08-22', @days=60

            select Date.DateKey, isnull(sum(Effort.Effort), 0) EffortToDate
            from Dim.Date
            left join (
            select DateKey, Effort
            from Fact.Effort
            join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey
            join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey
            where RootProject.ProjectName='Call Center'
            ) Effort on Effort.DateKey<=Date.DateKey
            where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate)
            group by Date.DateKey
        order by Date.DateKey

Effort Trend For a Specific Member

            declare @startDate datetime, @days int
            select @startDate='2009-08-22', @days=60

            select Date.DateKey,
            isnull(sum(Effort.Effort), 0) EffortToDate
            from Dim.Date
            left join (
            select Effort.DateKey, Effort.Effort
            from Fact.Effort
            join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey
            join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey
            join Dim.Member on Member.MemberKey=Effort.MemberKey
            where
            RootProject.ProjectName='Call Center'
            and Member.MemberName='Alfred Smith'
            ) Effort on Effort.DateKey<=Date.DateKey
            where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate)
            group by Date.DateKey
        order by Date.DateKey

Effort Trend for a Member Group

            declare @startDate datetime, @days int
            select @startDate='2009-08-22', @days=60

            select Date.DateKey,
            isnull(sum(Effort.Effort), 0) EffortToDate
            from Dim.Date
            left join (
            select Effort.DateKey, Effort.Effort
            from Fact.Effort
            join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey
            join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey
            join Bridge.MemberGroup B on B.MemberKey=Effort.MemberKey
            join Dim.MemberGroup on MemberGroup.MemberGroupKey=B.MemberGroupKey
            where
            RootProject.ProjectName='Call Center'
            and MemberGroup.MemberGroupName='Local Developers'
            ) Effort on Effort.DateKey<=Date.DateKey
            where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate)
            group by Date.DateKey
        order by Date.DateKey

Effort Trend for a Sprint

            declare @startDate datetime, @days int
            select @startDate='2009-08-22', @days=60

            select Date.DateKey,
            isnull(sum(Effort.Effort), 0) EffortToDate
            from Dim.Date
            left join (
            select DateKey, Effort
            from Fact.Effort
            join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey
            join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey
            join Dim.Iteration on Iteration.IterationKey=Effort.IterationKey
            where
            RootProject.ProjectName='Call Center'
            and IterationName='Month A 1st Half'
            ) Effort on Effort.DateKey<=Date.DateKey
            where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate)
            group by Date.DateKey
        order by Date.DateKey

Effort Trend for a Team

            declare @startDate datetime, @days int
            select @startDate='2009-08-22', @days=60

            select Date.DateKey,
            isnull(sum(Effort.Effort), 0) EffortToDate
            from Dim.Date
            left join (
            select DateKey, Effort
            from Fact.Effort
            join Tree.ProjectParent T on T.DescendantKey=Effort.ProjectKey
            join Dim.Project RootProject on RootProject.ProjectKey=T.AncestorKey
            join Dim.Team on Team.TeamKey=Effort.TeamKey
            where
            RootProject.ProjectName='Call Center'
            and TeamName='Team A'
            ) Effort on Effort.DateKey<=Date.DateKey
            where Date.Date>=@startDate and Date.Date<DATEADD(D, @days, @startDate)
            group by Date.DateKey
        order by Date.DateKey

Current Member Load

            declare @lastDateKey int     select @lastDateKey=max(DateKey) from Fact.Workitem

            select
            isnull(X.MemberKey,Y.MemberKey) MemberKey,
            AllocatedToDo,
            Done = isnull(Done, 0),
            MemberLoad = (isnull(AllocatedToDo,0) + isnull(Done, 0))
            from (
            select
            isnull(MemberKey, -1) MemberKey,
            IterationKey,
            sum(AllocatedDetailEstimate) AllocatedDetailEstimate,
            sum(AllocatedToDo) AllocatedToDo
            from (
            select
            Workitem.DateKey,
            Workitem.WorkitemKey,
            IterationKey,
            AllocatedDetailEstimate=DetailEstimate/isnull(OwnerCount, 1),
            AllocatedToDo=ToDo/isnull(OwnerCount, 1)
            from Fact.Workitem
            left join (
            select DateKey, WorkitemKey, count(MemberKey) OwnerCount
            from Bridge.WorkitemOwner
            group by DateKey, WorkitemKey
            ) Owners on Owners.DateKey=Workitem.DateKey and Owners.WorkitemKey=Workitem.WorkitemKey
            ) Workitem
            left join Bridge.WorkitemOwner on WorkitemOwner.WorkitemKey = Workitem.WorkitemKey and Workitem.DateKey = WorkitemOwner.DateKey
            where Workitem.DateKey = @lastDateKey
            group by
            WorkitemOwner.MemberKey,
            Workitem.IterationKey
            ) X
            full join (
            select MemberKey, IterationKey, sum(Effort) Done
            from Fact.Effort
            group by MemberKey, IterationKey
            ) Y on X.MemberKey=Y.MemberKey and X.IterationKey=Y.IterationKey
            join Dim.Iteration on Iteration.IterationKey=isnull(X.IterationKey,Y.IterationKey)
        where IterationAssetState='Active'