MiscellaneousQueries

This topic explains how to use the miscellaneous queries in Agility.

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