Skip to main content
Version: Early Access

CountQueries

This topic explains how to count the number of stories that meets some criteria.

Story Trend​

Trend the number of stories that meet some criteria

Story Trend by Priority​

            select DateKey, WorkitemPriorityName as [Priority], count(*) as [Count]
            from Fact.Workitem WI
            join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey
            join Dim.AssetState S on WI.PrimaryAssetStateKey = S.AssetStateKey
            join Dim.Project P on P.ProjectKey=T.AncestorKey
            join Dim.AssetType AT on WI.AssetTypeKey = AT.AssetTypeKey
            join Dim.WorkitemPriority WIP on WI.WorkitemPriorityKey=WIP.WorkitemPriorityKey
            where AssetTypeName ='Story' and ProjectName='Call Center' and AssetStateName = 'Active'
            group by DateKey, WorkitemPriorityName
            order by DateKey

Story Trend by Team​

            select DateKey, TeamName as [Team], count(*) as [Count]
            from Fact.Workitem WI
            join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey
            join Dim.AssetState S on WI.PrimaryAssetStateKey = S.AssetStateKey
            join Dim.Project P on P.ProjectKey=T.AncestorKey
            join Dim.AssetType AT on WI.AssetTypeKey = AT.AssetTypeKey
            join Dim.Team on Team.TeamKey=WI.TeamKey
            where AssetTypeName ='Story' and ProjectName='Call Center' and AssetStateName = 'Active'
            group by DateKey, TeamName
        order by DateKey

Task Trend​

Task Trend by Status​

            select DateKey, TaskStatusName, count(*) ActiveTaskCount
            from Fact.Workitem WI
            join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey
            join Dim.Project P on P.ProjectKey=T.AncestorKey
            join Dim.TaskStatus TS on TS.TaskStatusKey=WI.TaskStatusKey
            join Dim.AssetState S on S.AssetStateKey=WI.SecondaryAssetStateKey
            join Dim.AssetType AT on WI.AssetTypeKey = AT.AssetTypeKey
            where AssetTypeName='Task' and ProjectName='Release 1.0' and AssetStateName = 'Active'
            group by DateKey, TaskStatusName
        order by DateKey

Test Trend​

Test Trend by Status​

            select DateKey, count(*) TestCount, TestStatusName
            from Fact.Workitem WI
            join Dim.SecondaryWorkitem on WI.SecondaryWorkitemKey = SecondaryWorkitem.SecondaryWorkitemKey
            join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey
            join Dim.Project on Project.ProjectKey=T.AncestorKey
            join Dim.Team on Team.TeamKey=WI.TeamKey
            join Dim.AssetState on AssetState.AssetStateKey=WI.SecondaryAssetStateKey
            join Dim.TestStatus TS on WI.TestStatusKey=TS.TestStatusKey
            where SecondaryWorkitemType='Test' and ProjectName='Release 1.0' and AssetStateName = 'Active'
            group by DateKey, TestStatusName
        order by DateKey

Test Trend using a Custom Check box​

            select DateKey, DiscoveredAfterPlanningName [DiscoveredAfterPlanning], count(*) TestCount
            from Fact.Workitem WI
            join Dim.SecondaryWorkitem SWI on WI.SecondaryWorkitemKey = SWI.SecondaryWorkitemKey
            join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey
            join Dim.Project P on P.ProjectKey=T.AncestorKey
            join Dim.DiscoveredAfterPlanning DAP on DAP.DiscoveredAfterPlanningKey=WI.DiscoveredAfterPlanningKey
            join Dim.AssetState S on S.AssetStateKey=WI.SecondaryAssetStateKey
            where SecondaryWorkitemType='Test' and ProjectName='Release 1.0' and AssetStateName = 'Active'
            group by DateKey, DiscoveredAfterPlanningName
        order by DateKey

Defect Priority Trend​

            select DateKey, WorkitemPriorityName as [Priority], count(*) as [Count]
            from Fact.Workitem WI
            join Tree.ProjectParent T on T.DescendantKey=WI.ProjectKey
            join Dim.Project P on P.ProjectKey=T.AncestorKey
            join Dim.AssetType AT on WI.AssetTypeKey = AT.AssetTypeKey
            join Dim.AssetState S on WI.PrimaryAssetStateKey = S.AssetStateKey
            join Dim.WorkitemPriority WP on WI.WorkitemPriorityKey=WP.WorkitemPriorityKey
            where   AssetTypeName='Defect' and ProjectName='Call Center' and AssetStateName = 'Active'
            group by DateKey, WorkitemPriorityName
        order by DateKey