How to calculate the balance with SQL via using table transformer in JIRA?

[Oujy_lala]

Hi Team,

Trying to calculate the balance ticket of each month, the value should be accumulate, but seems the query ( in bold ) not works, can someone please help to check?  Thank you.

An example with screenshot attached:

in Oct 2024, New ticket is 24, resolve 0, Balance is 24

in Nov 2024, New ticket is 13, resolve 8, Balance is 5, but the exactly balance value i want should be balance of Oct 2024 + balance of Nov 2024 : 24+5=29

in Dec 2024, New ticket is 9, resolve 3, Balance is 6, the balance value i want is :

balance of Oct 2024 + balance of Nov 2024+ balance of Dec2024 = 24+5+6=35

......

and so forth.

I had use : sum(COALESCE(T1.'Count',0)) over(order by T1.'Created')
-sum(COALESCE(T2.'Count',0)) over(ORDER BY T1.'Created') AS 'Balance' 

but seems not work. 

-------------Here goes the SQL i had used ----------------------------------

SELECT
T1.'Created' AS 'Date',
COALESCE(sum(T1.'Count'),0) AS 'NEW Ticket',
COALESCE(sum(T2.'Count'),0) AS 'Resolved',
sum(COALESCE(T1.'Count',0)) over(order by T1.'Created')
-sum(COALESCE(T2.'Count',0)) over(ORDER BY T1.'Created') AS 'Balance' 
/*sum(T1.'Count') OVER(order by T1.'Created') AS 'Balance'*/

from T1 left OUTER JOIN T2 ON T1.'Created'=T2.'Resolved'

group by T1.'Created'

[Nikita Kamai · Accepted solution]

Hello,

Concerning our Table Filter, Charts & Spreadsheets for Confluence app, please, try the following SQL as an alternative:

CREATE TABLE TT1;
INSERT INTO TT1
SELECT
T1.'Created' AS 'Date',
COALESCE(sum(T1.'Count'),0) AS 'NEW Ticket',
COALESCE(sum(T2.'Count'),0) AS 'Resolved ',
sum(COALESCE(T1.'Count',0)) over(order by T1.'Created')
-sum(COALESCE(T2.'Count',0)) over(ORDER BY T1.'Created') AS 'Balance' 
/*sum(T1.'Count') OVER(order by T1.'Created') AS 'Balance'*/

from T1 left OUTER JOIN T2 ON T1.'Created'=T2.'Resolved'

group by T1.'Created';

SELECT TT1.'Date',TT1.'NEW Ticket', TT1.'Resolved ', TT1.'Balance' + (SELECT COALESCE(SUM(TT2.'Balance'), 0)
FROM TT1 AS TT2 WHERE TT2.'Date' < TT1.'Date') AS 'Balance' FROM TT1

NB. Also, please, make sure the date format in the macro settings is set to M yy, as your source output suggests to make the transformation work properly.

Best wishes,
Nikita

[Oujy_lala]

Hi Nikita,

Thank you so much, it worked! Now the result is ok.

One more question, for the SQL it still have a warning messages , do you have any idea about it?

[Oujy_lala]

Hi @Nikita Kamai ,

After update the date format in the macro settings is set to M yy, the warning message got removed. Thank you so much! Really appreciate your help.