ToDoQueries

This topic explains how to use teh to do queries in Agility.

Sprint Burndown

Sprint Burndown - Include Child Projects

            SELECT D.Date, SUM(WI.ToDo) [ToDo]
            FROM Fact.Workitem WI
            JOIN Tree.ProjectParent PP ON PP.DescendantKey = WI.ProjectKey
            JOIN Dim.Project P ON P.ProjectKey = PP.AncestorKey
            JOIN Dim.AssetState S ON WI.SecondaryAssetStateKey = S.AssetStateKey or S.AssetStateKey = WI.PrimaryAssetStateKey
            JOIN Dim.Date D ON D.DateKey = WI.DateKey
            JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
            WHERE S.AssetStateName = 'Active' AND P.ProjectName = 'Call Center' and Iterationname = 'Month C 1st Half'
            GROUP BY D.Date
        ORDER BY D.Date

Sprint Burndown - No Children

            SELECT  Date,
            SUM(ToDo) AS [ToDo]
            FROM    Fact.Workitem WI
            INNER JOIN Dim.Date D ON WI.DateKey = D.DateKey
            INNER JOIN Dim.AssetState S ON WI.SecondaryAssetStateKey = S.AssetStateKey or WI.PrimaryAssetStateKey = S.AssetStateKey
            INNER JOIN Dim.Project P ON WI.ProjectKey = WI.ProjectKey
            INNER JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
            WHERE   ( ProjectName = 'Release 1.0' )
            AND ( AssetStateName = 'Active' )
            and (Iterationname = 'Month C 1st Half')
            GROUP BY Date
        Order By Date

Sprint Burndown by Owner

            SELECT Date, SUM(ToDo) AS [ToDo]
            FROM  Fact.Workitem WI
            INNER JOIN Dim.Date D ON WI.DateKey = D.DateKey
            INNER JOIN Dim.AssetState S on WI.PrimaryAssetStateKey = S.AssetStateKey
            INNER JOIN Dim.Project P ON WI.ProjectKey = P.ProjectKey
            INNER JOIN Dim.Iteration I ON WI.IterationKey = I.IterationKey
            INNER JOIN Bridge.WorkitemOwner WO ON WI.WorkitemKey = WO.WorkitemKey AND WI.DateKey = WO.DateKey
            INNER JOIN Dim.Member M on WO.MemberKey = M.MemberKey
            WHERE   ( ProjectName = 'Release 1.0' )
            AND ( IterationName='Month C 1st Half' )
            AND ( AssetStateName = 'Active' )
            AND ( MemberName = 'Andre Agile' )
            GROUP BY Date
        ORDER BY Date