How to create days between dates for multiple scenarios in a confluence table

[John Allen]

I have a table constructed in confluence. It consists of different dates that a new Feature moves through the software development process from initiation to release. To the right, there are 4 duration columns that I want to show the date differential from phase to phase of the process.

Using the Table Transformer macro, the SQL query in the 2nd image below works. What I dont know is how to modify the SQL query to allow me to have 4 different date diff durations in the last 4 columns on the right?

1. Table prior to calculations.

2. SQL Query used. How to I add additional queries for the other 3 duration fields I want datediff outputs for?

3. I edited the worklog settings.

4. The output of the SQL query 

[Katerina Kovriga _Stiltsoft_]

Hi @John Allen ,

You've got the SQL query right - just add other DATEDIFF expressions subsequently:

SELECT *,
DATEDIFF(day, 'SRF Submitted', 'First Sprint Started') + 1 + " days" as 'SRF to First Sprint Duration',
DATEDIFF(day, 'First Sprint Started', 'UAT Signoff') + 1 + " days" as 'First Sprint to UAT Signoff Duration',
DATEDIFF(day, 'UAT Signoff', 'Production Release') + 1 + " days" as 'UAT Signoff to Release Duration',
DATEDIFF(day, 'SRF Submitted', 'Production Release') + 1 + " days" as 'Duration of Release'
FROM T*

And here is another example for you from the recent Community thread - how to set statuses based on dates' difference. 

[John Allen]

Hi @Katerina Kovriga _Stiltsoft_ 

Much simpler than I was thinking. That worked perfectly - thank you!!!

To improve this further, is there an easy way to display the text "No SRF" in the 'SRF to First Sprint Duration' column if the 'SRF Submitted' field is NULL? Same logic would apply for the other duration fields should any needed source data be null in the table.

Thank you!!!  

[Katerina Kovriga _Stiltsoft_]

You may try the following workaround - wrap the second Table Transformer macro on the top of the first one (so we won't mess up our query with DATEDIFF and will refer to a new big table). For Server/Data Center it is done directly, for Cloud - via the Table Toolbox macro.

Then you may use the following structure to replace the unwanted output with a more user-friendly text:

SELECT *,
CASE WHEN T1.'SRF to First Sprint Duration' LIKE "NaN days"
THEN "Not SRF"
ELSE T1.'SRF to First Sprint Duration'
END
AS 'SRF to First Sprint Duration'
FROM T*

[Katerina Kovriga _Stiltsoft_]

And if you use empty cells when there are no dates, you may use a slightly modified construction:

SELECT *,
CASE WHEN T1.'SRF Submitted' IS NULL
THEN "Not SRF"
ELSE T1.'SRF to First Sprint Duration'
END
AS 'SRF to First Sprint Duration'
FROM T*

Here we check not the result output, but the source column and then change the column with the DATEDIFF formula.

[Katerina Kovriga _Stiltsoft_]

Hi there,

As this question mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

The new macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit mode.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.