Calculating between Weeks and Days Table Transformer

[Holly Louise Perry]

Hi! 

I have a table in which I would like to calculate the difference between Weeks/Days/Hours/Minutes in the "Average Time In Backlog" and the "Average Time Created To Backlog" columns, and I was wondering if that is possible using Table Transformer/SQL. 

Date	Number Of Issues	Average Time Created To Backlog	Average Time In Backlog
01 Jan 2022	61	20w 3d 7h 33m	48w 5d 5h 7m
01 Feb 2022	68	20w 6d 9h 1m	43w 4d 25m
01 Mar 2022	77	23w 2d 12h 15m	38w 6d 17h 28m

This is my query, and I've tried varying Formatting options, but I can't seem to get it to work. 

SELECT T1.'Date', T1.'Average Time In Backlog', T1.'Average Time Created To Backlog',
(T1.'Average Time Created To Backlog' - T1.'Average Time In Backlog') AS 'Difference'
FROM T1

Any help would be greatly appreciated! :) Thank you so much! 
Best, Holly

[Natalie Paramonova [Stiltsoft] · Accepted solution]

Hi @Holly Louise Perry ,

Please add the "FORMATWORKLOG" function before counting the time difference:

SELECT FORMATWORKLOG(T1.'Time 2' - T1.'Time 1') AS 'Difference'
FROM T1

Besides you may go to the Table Transformer macro settings -> Settings tab -> Set worklog settings menu and check the parameters according to your case.

[Holly Louise Perry]

This is great! Thank you! Do you happen to know if there's a way to calculate a percentage of change between time (ie increase in time or decrease?)

I've done this with regular numbers in the past, but not with time. Here's the code I used for when I've done it with numbers (TT1 & TT2 were numbers). 

SELECT TT1.*, TT1.'Date',
TT2.'Week' AS 'Previous week',
((TT1.'Overall Bug Badness Score'-TT2.'Overall Bug Badness Score')/TT2.'Overall Bug Badness Score')*100 AS 'Weekly Change'
FROM T1 AS TT1
LEFT JOIN T1 AS TT2 ON TT1.'Week' = (TT2.'Week' + 1)

[Katerina Rudkovskaya _Stiltsoft_]

Hi @Holly Louise Perry, 

To count percent for the case, try to remove the "FORMATWORKLOG" function and create a required formula as you would do for regular numbers. It may look like this, for example:

SELECT *,
(T1.'Time 2' - T1.'Time 1')/T1.'Time 2'*100 AS 'Percent'
FROM T1

Then you may go to the Settings tab and choose how many decimal places you want to show.

[Holly Louise Perry]

Thank you so much! I was concerned that time wouldn't do it. This worked perfectly! Thank you both! :)