Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

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

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.
Sep 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.

Thanks! It works well.

Suggest an answer

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

Atlassian Community Events