Skip to main content
System StatusContact Support
VersionOne Community

MiscellaneousQueries

This feature is available in Enterprise and Ultimate editions.

Projects a Member can view

select P.ProjectKey,ProjectName
from Dim.Project as P
    join Bridge.ProjectMember PM on PM.ProjectKey = P.ProjectKey
    join Dim.Member M on PM.MemberKey = M.MemberKey
where MemberName = 'Andre Agile'
order by P.ProjectPath

 

Cycle Time

declare @beginValue nvarchar(25), @endValue nvarchar(25);
set @beginValue='In Progress';
set @endValue='Accepted';

WITH PWIDate AS (
select ProjectKey,Estimate, FirstDateKey, LastDateKey
from
(SELECT ProjectKey,WorkitemKey,Estimate,MIN(DateKey) AS FirstDateKey
    FROM Fact.Workitem as PWI
    INNER JOIN Dim.WorkitemStatus WIS ON PWI.WorkitemStatusKey = WIS.WorkitemStatusKey
    INNER JOIN Dim.AssetType AT ON PWI.AssetTypeKey = AT.AssetTypeKey
    where WorkitemStatusName=@beginValue and (AssetTypeName = 'Story' or AssetTypeName = 'Defect')
    GROUP BY ProjectKey,WorkitemKey,Estimate) A 
join
(SELECT WorkitemKey, Min(DateKey) AS LastDateKey
    FROM Fact.Workitem as PWI
    INNER JOIN Dim.WorkitemStatus WIS ON PWI.WorkitemStatusKey = WIS.WorkitemStatusKey
    INNER JOIN Dim.AssetType AT ON PWI.AssetTypeKey = AT.AssetTypeKey
    where WorkitemStatusName=@endValue and (AssetTypeName = 'Story' or AssetTypeName = 'Defect')
    GROUP BY WorkitemKey)B on A.WorkitemKey=B.WorkitemKey)
, PWIDateDiff AS (   
    SELECT ProjectKey,Estimate,DATEDIFF(DD,DMIN.Date,DMAX.Date) CycleTime
    FROM PWIDate
    INNER JOIN Dim.Date DMIN ON PWIDate.FirstDateKey = DMIN.DateKey
    INNER JOIN Dim.Date DMAX ON PWIDate.LastDateKey = DMAX.DateKey)

SELECT Estimate,AVG(CycleTime) [AverageCycleTimeinDays]
FROM PWIDateDiff
join Dim.Project on PWIDateDiff.ProjectKey = Project.ProjectKey
where ProjectName='Release 1.0'
GROUP BY Estimate
Order by Estimate

 

CycleTime with Workitem Counts

select Estimate, Avg(CycleTime) AverageCycleTime, count(*) WorkitemCount
from
(
    select
        PrimaryWorkitem.PrimaryWorkitemKey,
        CycleBeginKey, BeginDate.Date CycleBeginDate,
        CycleEndKey, EndDate.Date CycleEndDate,
        CycleTime=cast(DateDiff(D, BeginDate.Date, EndDate.Date) as float),
        Estimate=(select top 1 Estimate from Fact.PrimaryWorkitem P where P.PrimaryWorkitemKey=PrimaryWorkitem.PrimaryWorkitemKey)
    from Dim.PrimaryWorkitem
    join (
        select min(DateKey) CycleBeginKey, PrimaryWorkitemKey
        from Fact.PrimaryWorkitem
        join Dim.WorkitemStatus on WorkitemStatus.WorkitemStatusKey=PrimaryWorkitem.WorkitemStatusKey
        where WorkitemStatusName in ('In Progress', 'Done')
        group by PrimaryWorkitemKey
    ) B on B.PrimaryWorkitemKey=PrimaryWorkitem.PrimaryWorkitemKey
    join (
        select min(DateKey) CycleEndKey, PrimaryWorkitemKey
        from Fact.PrimaryWorkitem
        join Dim.WorkitemStatus on WorkitemStatus.WorkitemStatusKey=PrimaryWorkitem.WorkitemStatusKey
        where WorkitemStatusName in ('Accepted')
        group by PrimaryWorkitemKey
    ) E on E.PrimaryWorkitemKey=PrimaryWorkitem.PrimaryWorkitemKey
    join Dim.Date BeginDate on BeginDate.DateKey=CycleBeginKey
    join Dim.Date EndDate on EndDate.DateKey=CycleEndKey
    --where BeginDate.Date>'2009-08-27'
) CycleTimes
group by Estimate
  • Was this article helpful?