mVerve Partner Article

This KB article was developed by the mVerve team in consultation with the Inflectra team.

Objective

The query aims to provide insights into the aging of requirements within the project over time. By categorizing requirements into different cohorts based on their duration since creation and last update, the analysis helps identify trends and patterns in requirement development and maintenance. Additionally, by focusing on requirements flagged as 'Developed' and excluding deleted items, the analysis ensures that only relevant and active data is considered, leading to more accurate and actionable insights for project management and decision-making purposes.

Steps

  • To create the custom report you need to:

    • Go to Administration  > Edit Graphs

    • Create a new Graph, give it a name

    • Choose to add a custom graph

  • Create a graph using a modified query

  • The following Entity SQL (ESQL) example modifies the custom report

SQL Query

Given below is the SQL Query

SELECT '01-Jan' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT '02-Feb' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 2 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 2 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 2 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 2 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT '03-March' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 3 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 3 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 3 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 3 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '04-April' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 4 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 4 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 4 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 4 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '05-May' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 5 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 5 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 5 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 5 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '06-June' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 6 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 6 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 6 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 6 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '07-July' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 7 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 7 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 7 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 7 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '08-August' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 8 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 8 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 8 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 8 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '09-Sept' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 9 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 9 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 9 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 9 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '10-Oct' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 10 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 10 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 10 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 10 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '11-November' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 11 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 11 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 11 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 11 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
    R.REQUIREMENT_STATUS_NAME = 'Developed')
UNION
(SELECT
    '12-December' AS month,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 12 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 1 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 30 THEN 1 ELSE 0 END) AS Under30days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 12 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 30 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 60 THEN 1 ELSE 0 END) AS Under60days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 12 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 60 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) < 90 THEN 1 ELSE 0 END) AS Under90days,
    SUM(CASE WHEN MONTH(R.CREATION_DATE) = 12 AND DiffDays(R.CREATION_DATE, R.LAST_UPDATE_DATE) >= 90 THEN 1 ELSE 0 END) AS Above90days
FROM SpiraTestEntities.R_Requirements AS R
WHERE
    R.IS_DELETED = False
    AND
R.REQUIREMENT_STATUS_NAME = 'Developed')

Query Explanation

  • The query employs the UNION operator to amalgamate results from multiple SELECT statements, each representing data for distinct months. It categorizes requirements into four cohorts based on the duration between their creation and last update: less than 30 days, 30 to 60 days, 60 to 90 days, and over 90 days. The analysis focuses solely on requirements flagged as 'Developed' and not deleted.
  • The query features conditional counting utilizing the SUM function in conjunction with a CASE statement, enabling the calculation of counts for each time bucket.
  • Each SELECT statement computes counts for the aforementioned time buckets, leveraging a custom function (DiffDays) to determine the difference in days between requirement creation and last update.
  • Data filtering is executed through the WHERE clause, restricting results to non-deleted requirements (R.IS_DELETED = False) and those designated as 'Developed' (R.REQUIREMENT_STATUS_NAME = 'Developed').

Output - Data Display

Given below is the example of the data set for this query. Please note that the display only shows a partial list in this view.  For instance, even though there is data for the months of August to December, the data view below does not show them.
mVerve KB1 Trend Reporting Data Set

Output - Graph Display

Given below is the illustrative graph. This graph can be added to the Reporting section for management review.

mVerve KB1 Trend Reporting Bar Graph