Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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.

Photo 0.JPG

 

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

Photo 1.JPG

 

3. I edited the worklog settings.

 

Photo 2.JPG

 

 

4. The output of the SQL query 

Photo 3.JPG

1 answer

Hi @John Allen ,

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

Wed 4-1.png

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*

Wed 4-2.png

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

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!!!  

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.

Wed 5-1.png

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*

Wed 5-2.png

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

Wed 5-3.png

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*

Wed 5-4.png

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence Cloud

🏠 Say hello to the new Confluence Home!

Hi Atlassian Community, My name is DJ Chung, and I’m a Product Manager on the Confluence Cloud team. Today, I’m excited to share a new and improved version of Home. The new Home helps you ...

41,352 views 28 130
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you