Skip to main content
System StatusContact Support
VersionOne Community

EffortQueries

  1. Last updated
  2. Save as PDF

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'