SQL Query to calculate the delta between two values and tally the total of the column

Veronica Brooker

Hi everyone, 

I have a table 9 column table in Confluence. Two of the columns are numeric with a total sum applied. 

The use case is:
Column A: Service Credits - values are debits. 
Column B: Total Service Credits Accrued - values are credits.  
With each new entry into column A, I need to subtract that value from the running total in  column B.
Both columns should also have a total sum of at the footer of the table. 

I have applied a Table Transformer, but I do not know the SQL query to do product this outcome. Can someone please share this with me or tell me if there is another Macro/way to accomplish this? 

Katerina Kovriga {Stiltsoft}

Hi @Veronica Brooker ,

Please follow these steps: wrap your table into the Table Toolbox macro.

Then go to the macro settings and wrap your table subsequently into the Table Transformer and into the Table Filter macro.

Then go to to the Table Transformer macro and use the following SQL query:

SELECT *, ('Total Service Credits Accrued' + 'Service Credits') AS 'New Column' FROM T*

Go to the Settings tab and set the corresponding Date format:

Then go to the Table Filter macro settings and select the columns where you want to count the totals:

Hope this may help your case.

P.S. Here you can find more examples how to use the Table Transformer macro - use cases, source tables and SQL queries.