Average Velocity with Table Transformer - SELECT TOP in subquery is not working

basquang
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
September 25, 2023

Hi all, 

I have a table like this:

Sprint

Sprint Name

Committed

Completed

Sprint 01Team: Sprint 122
Sprint 02Team: Sprint 22424
Sprint 03Team: Sprint 300
Sprint 04Team: Sprint 4138
Sprint 05Team: Sprint 51515
Sprint 06 Team: Sprint 6160
Sprint 07 Team: Sprint 72020
Sprint 08 Team: Sprint 82121
Sprint 09Team: Sprint 999
Sprint 10Team: Sprint 102214
Sprint 11 Team: Sprint 113733
Sprint 12Team: Sprint 122626
Sprint 13Team: Sprint 131722
Sprint 14Team: Sprint 141919
Sprint 15Team: Sprint 15  
Sprint 16Team: Sprint 162416
Sprint 17Team: Sprint 172424
Sprint 18Team: Sprint 18  

I would like to calculate velocity based on the last 6 sprints. I'm using a Table Transformer with the query below but seems that the SELECT TOP query always returns full rows. So that the result is not correct. How do I do it properly?

 

SELECT TT1.'No',
TT1.'Sprint',
TT1.'Sprint Name',
TT1.'Committed',
TT1.'Completed',
(SELECT SUM(TT2.'Completed') FROM T1 AS TT2 WHERE TT2.'Sprint'<= TT1.'Sprint') AS 'Total Burned',
(SELECT AVG(TT2.'Completed') FROM T1 AS TT2 WHERE TT2.'Sprint'<= TT1.'Sprint') AS 'Velocity',
(SELECT TOP 6 AVG(TT2.'Completed')
FROM T1 AS TT2
WHERE TT2.'Completed'>=0
AND TT2.'No' <= TT1.'No'
ORDER BY TT2.'No' DESC) AS 'Velocity (6) Temp',
TT1.'Closed Defect',
TT1.'Closed Task'
FROM T1 AS TT1

 

@Katerina Kovriga _Stiltsoft_ Please help

2 answers

1 accepted

3 votes
Answer accepted
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 25, 2023

Hi @basquang ,

Indeed your case is not trivial. :) 

Please try the following SQL query, hope it will help your case:

SELECT TT1.'No',
TT1.'Sprint',
TT1.'Sprint Name',
TT1.'Committed',
TT1.'Completed',
(SELECT SUM(TT2.'Completed') FROM T1 AS TT2 WHERE TT2.'Sprint'<= TT1.'Sprint') AS 'Total Burned',
(SELECT AVG(TT2.'Completed') FROM T1 AS TT2 WHERE TT2.'Sprint'<= TT1.'Sprint') AS 'Velocity',
(SELECT AVG(TT2.'Completed')
FROM T1 AS TT2
WHERE TT2.'Completed'>=0
AND TT2.'No' <= TT1.'No' AND (TT2.'No' + 5) >= TT1.'No'
ORDER BY TT2.'No' DESC) AS 'Velocity (6) Temp'
FROM T1 AS TT1

Here we excluded the TT1.'Closed Defect', TT1.'Closed Task', etc. columns while testing the query (they were not present in the original table), so you may add them back to display on the page.

0 votes
basquang
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
September 26, 2023

Thanks! It works well.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
FREE
PERMISSIONS LEVEL
Product Admin
TAGS
AUG Leaders

Atlassian Community Events