Test Execution Cumulative Report by Day/Week/Month

The query helps you generate a report that shows the cumulative data on a number of test executions carried out over time along with the execution results. It creates a cumulative report in QMetry with a stacked column/bar chart to display the number of test executions done daily/weekly/monthly in a cumulative way. Here, the count of the previous day gets added to the count of the current date and so on.

SQL Query

SELECT 
    (CASE
        WHEN @viewby in ('Day') THEN b.selectionCriteria
        WHEN @viewby in ('Week') THEN CONCAT(STR_TO_DATE(CONCAT(b.selectionCriteria,' Monday'),'%x%v %W'),'-',STR_TO_DATE(CONCAT(b.selectionCriteria,' Sunday'),'%X%V %W'))
        WHEN @viewby in ('Month') THEN CONCAT(MONTHNAME(STR_TO_DATE(RIGHT(b.selectionCriteria, 2), '%m')),'-',LEFT(b.selectionCriteria,4))
        WHEN @viewby in ('Year') THEN b.selectionCriteria
    END) AS `Date Range`,
    b.tcExecutionStatusName AS `countofTC`,
    b.countofval AS `Count of TC by Execution Status`
FROM
    (SELECT 
        a.tcExecutionStatusName,
            a.selectionCriteria,
            a.cn,
            IF(@runstat = a.tcExecutionStatusName, ROUND(@csum:=@csum + COALESCE(a.cn)), ROUND(@csum:=COALESCE(a.cn))) countofval,
            @runstat:=a.tcExecutionStatusName
    FROM
        (SELECT 
        dateData.selected_date,
            dateData.tcExecutionStatusName,
            COUNT(COALESCE(tcExecutionID)) AS cn,
            selectionCriteria
    FROM
        (SELECT 
        dtbl.selected_date,
            temp.tcExecutionStatusName,
            dtbl.selectionCriteria
    FROM
        (SELECT 
        selected_date,
            (CASE
                WHEN @viewby in ('Day') THEN selected_date
                WHEN @viewby in ('Week') THEN YEARWEEK(selected_date, 3)
                WHEN @viewby in ('Month') THEN EXTRACT(YEAR_MONTH FROM selected_date)
                WHEN @viewby in ('Year') THEN EXTRACT(YEAR FROM selected_date)
            END) AS selectionCriteria
    FROM
        (SELECT 
        ADDDATE(`@Filter.Date Start`, t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) selected_date
    FROM
        (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v,(SELECT @viewby:= `@Filter.View By`) as v1
    WHERE
        DATE(selected_date) BETWEEN `@Filter.Date Start` AND `@Filter.Date End`) AS dtbl
    CROSS JOIN (SELECT DISTINCT
        tcExecutionStatusName
    FROM
        testexecutions tcr WHERE tcr.projectID in (`@Filter.project`)) AS temp) AS dateData
    LEFT JOIN (SELECT 
        tcr.tcExecutionStatusName,
            DATE(tcr.tcExecutionStartTime) AS dat,
            tcr.tcExecutionID,
            tcr.projectID
    FROM
        testexecutions tcr
    WHERE
        tcExecutionStartTime IS NOT NULL AND tcr.projectID in (`@Filter.project`) AND tcr.releaseID in (`@Filter.Release`) AND tcr.cycleID in (`@Filter.Cycle`) AND tcr.platformID in (`@Filter.Platform`)) raw ON raw.dat = dateData.selected_date
        AND raw.tcExecutionStatusName = dateData.tcExecutionStatusName
    GROUP BY tcExecutionStatusName , selectionCriteria ORDER BY tcExecutionStatusName ASC) a
    JOIN (SELECT @csum:=0) r
    JOIN (SELECT @runstat:=0) p) b
    WHERE (select @viewby := `@Filter.View By`) <> ''
ORDER BY b.selectionCriteria , b.tcExecutionStatusName;

Apply the Filter
You can filter the report based on multiple fields defined in filters. The Date filters get the report between two different dates. The View By filter gives options to generate and view the report by day/week/ month.

Query Output