EffortQueries
This feature is available in Enterprise and Ultimate editions.
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'