sprint_definitions.yml

fields:
    project_name:
        description: "Identifer of a project or team"
        jira:
            table: project
            column: [project_id, name]
            field: CASE WHEN ${project_ids} THEN CONCAT('Proj', ${t("project")}.project_id) ELSE ${t("project")}.name END
        tfs:
            table: tfs_team
            column: [team_id, name]
            field: CASE WHEN ${project_ids} THEN CONCAT('Team', ${t("project")}.team_id) ELSE ${t("project")}.name END
    project_display_name:
        description: "Human-readable description of the project or team"
        jira:
            table: project
            column: [quality_display_name, jira_name, name]
            field: CASE WHEN ${project_ids} THEN NULL ELSE CASE WHEN COALESCE(${t("project")}.quality_display_name, ${t("project")}.name) = ${t("project")}.name THEN ${t("project")}.jira_name ELSE ${t("project")}.quality_display_name END END
        tfs:
            table: tfs_team
            column: [name]
            field: CASE WHEN ${project_ids} THEN NULL ELSE ${t("project")}.name END
    sprint_id:
        description: "Ascending identifier of a sprint"
        table: [sprint, tfs_sprint, fixversion]
        column: [sprint_id]
        field: COALESCE(${f(join_cols, "sprint", mask=2, alias="alias")}, 0)
    sprint_name:
        description: "Human-readable description of a sprint"
        table: [sprint, tfs_sprint, fixversion]
        column: [name]
        field: "CASE WHEN ${sprint_ids} THEN CONCAT('Sprint ', COALESCE(${f(join_cols, \"sprint\", mask=2, alias=\"alias\")}, 0)) ELSE ${t(\"sprint\")}.name END"
    issue_key:
        description: "Human-readable identifier of an issue"
        jira:
            table: issue
            column: [issue_id, key]
            field: CASE WHEN ${project_ids} THEN CONCAT('#', ${t("issue")}.issue_id) ELSE ${t("issue")}.key END
        tfs:
            table: tfs_work_item
            column: [issue_id]
            field: CONCAT('#', ${t("issue")}.issue_id)
    sprint_days:
        description: "Number of days in a sprint"
        jira:
            table: [sprint, tfs_sprint]
            column: [end_date, start_date]
            field: EXTRACT(day FROM ${t("sprint")}.end_date - ${t("sprint")}.start_date)
        jira_version:
            table: [fixversion]
            column: [start_date, release_date]
            field: ${t("sprint")}.release_date - ${t("sprint")}.start_date
    sprint_weekdays:
        description: "Number of weekdays in a sprint"
        table: [sprint, tfs_sprint]
        column: [start_date]
        field: CAST(EXTRACT(day FROM ${s(sprint_close)} - CAST(${t("sprint")}.start_date AS TIMESTAMP)) * 5.0 / 7 AS INT) + 1 - 2 * CAST(TIMESTAMP_TO_STR(${s(sprint_close)}, '%u') < TIMESTAMP_TO_STR(CAST(${t("sprint")}.start_date AS TIMESTAMP), '%u') AS INT)
    velocity:
        description: "Velocity of a sprint (or average of multiple sprints in aggregate queries)"
        table: [issue, tfs_work_item]
        column: [story_points]
        field: CAST(SUM(${t("issue")}.story_points) AS float)/(CASE WHEN sprint_days <> 0 THEN CEIL(sprint_days/7.0*5) ELSE 1 END)
    sprint_open:
        description: "The open date of a sprint"
        table: [sprint, fixversion, tfs_sprint]
        column: [start_date]
        field: COALESCE(CAST(${t("sprint")}.start_date AS TIMESTAMP), ${current_timestamp})
    sprint_close:
        description: "The close date of a sprint"
        jira:
            table: sprint
            column: [end_date, complete_date]
            field: CASE WHEN ${t("sprint")}.complete_date IS NOT NULL AND CAST(${t("sprint")}.complete_date AS DATE) < CAST(${t("sprint")}.end_date AS DATE) THEN ${t("sprint")}.complete_date ELSE ${t("sprint")}.end_date END
        jira_version:
            table: fixversion
            column: [release_date]
            field: CAST(${t("sprint")}.release_date AS TIMESTAMP)
        tfs:
            table: tfs_sprint
            column: [end_date]
            field: ${t("sprint")}.end_date
    sprint_board:
        description: "Board ID on which the sprint is shown"
        jira:
            table: sprint
            column: [board_id]
            field: ${t("sprint")}.board_id
        tfs:
            table: tfs_sprint
            column: []
            field: 'NULL'
    fix_version:
        description: "The identifier of the version in which the resolution of issue should be released"
        jira:
            table: issue
            column: [fixversion]
            field: ${t("issue")}.fixversion
        tfs:
            table: tfs_work_item
            column: []
            field: 'NULL'
    story_points:
        description: "The number of planned points of an issue"
        table: [issue, tfs_work_item]
        column: [story_points]
        field: CASE WHEN ${t("issue")}.story_points IN (-5, -1, 99, 100, 122, 999) THEN 0 ELSE ${t("issue")}.story_points END
    developer_internal:
        description: "Whether the developer has an internal email address"
        jira:
            table: developer
            column: [local_domain]
            field: ${t("developer")}.local_domain
        tfs:
            table: tfs_developer
            column: [email]
            field: ${t("developer")}.email LIKE '%\%'
    developer_issue_name:
        description: "Name of the developer as mentioned in issue tables (reporter, assignee, updater)"
        jira:
            table: developer
            column: [name]
            field: ${t("developer")}.name
        tfs:
            table: tfs_developer
            column: [display_name]
            field: ${t("developer")}.display_name
    issue_status:
        description: "Human-readable name of an issue status"
        jira:
            table: status
            column: [name]
            field: status.name
        tfs:
            table: tfs_work_item
            column: [status]
            field: ${t("issue")}.status
    issue_resolution:
        description: "Human-readable name of an issue resolution"
        jira:
            table: resolution
            column: [name]
            field: resolution.name
        tfs:
            table: tfs_work_item
            column: [status]
            field: CASE WHEN ${s(issue_done)} THEN ${t("issue")}.status ELSE null END
    issue_status_resolution_join:
        description: "Join statement for status and resolution names"
        jira:
            table: issue
            column: [status, resolution]
            field: LEFT JOIN gros.status ON ${t("issue")}.status = status.id LEFT JOIN gros.resolution ON ${t("issue")}.resolution = resolution.id
        tfs:
            table: tfs_work_item
            column: []
            field: ''
conditions:
    project_recent:
        description: "Projects that have recent sprints"
        jira:
            table: [sprint, tfs_sprint]
            column: [start_date]
            condition: MAX(${t("sprint")}.start_date) >= CAST('${date}' AS TIMESTAMP)
        jira_version:
            table: fixversion
            column: [start_date]
            condition: MAX(${t("sprint")}.start_date) >= CAST('${date}' AS DATE)
    project_core:
        description: "Projects that are not support teams"
        jira:
            table: project
            column: [is_support_team]
            condition: NOT COALESCE(${t("project")}.is_support_team, false)
        tfs:
            table: tfs_team
            column: [name]
            condition: ${t("project")}.name <> 'IT Squad'
    project_main:
        description: "Projects that do not have a parent project"
        jira:
            table: project
            column: [main_project]
            condition: CASE WHEN ${t("project")}.main_project IS NULL THEN true ELSE false END
        tfs:
            table: tfs_team
            column: [name]
            condition: 'true'
    issue_open:
        description: "Issues that are not yet determined to have no work left"
        jira:
            table: issue
            column: [status]
            condition: ${t("issue")}.status NOT IN (5,6,10008)
        tfs:
            table: tfs_work_item
            column: [status]
            condition: ${t("issue")}.status NOT IN ('Closed', 'Done', 'done', 'Removed', 'Completed')
    issue_in_progress:
        description: "Issues that are in progress"
        jira:
            table: issue
            column: [status]
            condition: ${t("issue")}.status IN (3, 10108)
        tfs:
            table: tfs_work_item
            column: [status]
            condition: ${t("issue")}.status = 'In Progress'
    issue_done:
        description: "Issues that are done according to the team's definition of done"
        jira:
            table: issue
            column: [resolution, status]
            condition: (${t("issue")}.resolution IN (1, 10000) OR ${t("issue")}.status IN (6, 10008))
        tfs:
            table: tfs_work_item
            column: [status]
            condition: ${t("issue")}.status IN ('Closed', 'Done', 'done', 'Removed', 'Completed')
    issue_not_done:
        description: "Issues that are not (yet) done according to the team's definition of done"
        jira:
            table: issue
            column: [resolution, status]
            condition: COALESCE(${t("issue")}.resolution, 0) NOT IN (1, 10000) AND COALESCE(${t("issue")}.status, 0) NOT IN (6, 10008)
        tfs:
            table: tfs_work_item
            column: [status]
            condition: ${t("issue")}.status NOT IN ('Closed', 'Done', 'done', 'Removed', 'Completed')
    issue_closed:
        description: "Issues where no further work is ever expected"
        jira:
            table: issue
            column: [status]
            condition: ${t("issue")}.status IN (6, 10008)
        tfs:
            table: tfs_work_item
            column: [status]
            condition: ${t("issue")}.status = 'Closed'
    issue_excluded:
        description: "Issues that are removed from consideration"
        jira:
            table: issue
            column: [status, resolution]
            condition: ${t("issue")}.status IN (5, 6) AND ${t("issue")}.resolution IN (2, 3, 4, 5, 7, 17, 18, 19, 20)
        jira_version:
            table: issue
            column: [status, resolution]
            condition: ${t("issue")}.status = 10707 AND ${t("issue")}.resolution = 10001
        tfs:
            table: tfs_work_item
            column: [status]
            condition: ${t("issue")}.status = 'Removed'
    issue_overdue:
        description: "Issues that are in a certain state after the sprint ended (use in combination with other conditions)"
        table: [issue, sprint, max_issue, tfs_work_item, tfs_sprint]
        column: [update, changelog_id]
        condition: (${t("issue")}.updated > ${t("sprint")}.end_date OR (${t("issue")}.changelog_id = ${t("max_issue")}.changelog_id AND CAST(${t("sprint")}.end_date AS TIMESTAMP) < ${current_timestamp}))
    issue_story:
        description: "Issues that are stories"
        jira:
            table: issue
            column: [type]
            condition: ${t("issue")}."type" = 7
        jira_version:
            table: issue
            column: [type]
            condition: ${t("issue")}."type" IN (3, 10100)
        tfs:
            table: tfs_work_item
            column: [type]
            condition: ${t("issue")}."type" = 'Product Backlog Item'
    issue_story_subtask:
        description: "Issues that are stories or subtasks"
        jira:
            table: issue
            column: [type]
            condition: ${t("issue")}."type" IN (5,7)
        jira_version:
            table: issue
            column: [type]
            condition: ${t("issue")}."type" IN (3, 5, 10100)
        tfs:
            table: tfs_work_item
            column: [type]
            condition: ${t("issue")}."type" IN ('Product Backlog Item')
    issue_other:
        description: "Issues that are not stories or subtasks"
        jira:
            table: issue
            column: [type]
            condition: ${t("issue")}."type" NOT IN (5,7)
        jira_version:
            table: issue
            column: [type]
            condition: ${t("issue")}."type" NOT IN (3, 5, 10100)
        tfs:
            table: tfs_work_item
            column: [type]
            condition: ${t("issue")}."type" NOT IN ('Product Backlog Item')
    issue_bug:
        description: "Issues that are bugs"
        jira:
            table: issue
            column: [type]
            condition: ${t("issue")}."type" IN (1, 12)
        tfs:
            table: tfs_work_item
            column: [type]
            condition: ${t("issue")}."type" = 'Bug'
    issue_test_case:
        description: "Issues that are test cases"
        jira:
            table: issue
            column: [type]
            condition: ${t("issue")}."type" IN (9, 10, 10301)
        tfs:
            table: tfs_work_item
            column: [type]
            condition: ${t("issue")}."type" IN ('Test Case', 'Test Suite', 'Test Plan')
    sprint_closed:
        description: "Sprints that have been ended or completed"
        jira:
            table: sprint
            column: [end_date, complete_date]
            condition: COALESCE(COALESCE(${t("sprint")}.complete_date, ${t("sprint")}.end_date), ${current_timestamp}) < ${current_timestamp}
        jira_version:
            table: fixversion
            column: [release_date]
            condition: COALESCE(CAST(${t("sprint")}.release_date AS TIMESTAMP), ${current_timestamp}) < ${current_timestamp}
        tfs:
            table: tfs_sprint
            column: [end_date]
            condition: COALESCE(${t("sprint")}.end_date, ${current_timestamp}) < ${current_timestamp}
    planned_early:
        description: "Earliest time for stories that are planned for a sprint to start work on"
        table: [sprint, tfs_sprint]
        column: [start_date]
        condition: COALESCE(CAST(${t("sprint")}.start_date AS TIMESTAMP), ${current_timestamp}) - interval '1' day
    planned_end:
        description: "Latest time for stories to be planned for a sprint"
        table: [sprint, tfs_sprint]
        column: [start_date]
        condition: COALESCE(CAST(${t("sprint")}.start_date AS TIMESTAMP), ${current_timestamp}) + interval '1' day
    planned_late:
        description: "Latest time for stories that are planned for a sprint to have been worked on"
        jira:
            table: [sprint, tfs_sprint]
            column: [start_date, end_date]
            condition: COALESCE(${t("sprint")}.start_date + (${t("sprint")}.end_date - ${t("sprint")}.start_date)/7, ${current_timestamp})
        jira_version:
            table: [fixversion]
            column: [start_date, release_date]
            condition: COALESCE(CAST(${t("sprint")}.start_date + (${t("sprint")}.release_date - ${t("sprint")}.start_date)/7 AS TIMESTAMP), ${current_timestamp})