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

basquang

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

Stiltsoft support

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.