Hi all,
I have a table like this:
Sprint | Sprint Name | Committed | Completed |
Sprint 01 | Team: Sprint 1 | 2 | 2 |
Sprint 02 | Team: Sprint 2 | 24 | 24 |
Sprint 03 | Team: Sprint 3 | 0 | 0 |
Sprint 04 | Team: Sprint 4 | 13 | 8 |
Sprint 05 | Team: Sprint 5 | 15 | 15 |
Sprint 06 | Team: Sprint 6 | 16 | 0 |
Sprint 07 | Team: Sprint 7 | 20 | 20 |
Sprint 08 | Team: Sprint 8 | 21 | 21 |
Sprint 09 | Team: Sprint 9 | 9 | 9 |
Sprint 10 | Team: Sprint 10 | 22 | 14 |
Sprint 11 | Team: Sprint 11 | 37 | 33 |
Sprint 12 | Team: Sprint 12 | 26 | 26 |
Sprint 13 | Team: Sprint 13 | 17 | 22 |
Sprint 14 | Team: Sprint 14 | 19 | 19 |
Sprint 15 | Team: Sprint 15 | ||
Sprint 16 | Team: Sprint 16 | 24 | 16 |
Sprint 17 | Team: Sprint 17 | 24 | 24 |
Sprint 18 | Team: 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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.