Table Transformer Macro - If else condition

Shathya Bhama V R October 20, 2020

I am using confluence to track the risks and the table has these columns:

  1. Risk Detail
  2. Open Date
  3. Status - (Open/Closed)
  4. Closed Date
  5. Aging (in days)
  6. Remarks

I am calculating the aging by using the below query for the open issues. 

SELECT *, ROUND(DATEDIFF(day,'Open Date',CURRENT_TIMESTAMP))+ " day(s)" as 'Aging' from T1

For the closed issues, I need to find the elapsed time between Open and Closed dates. 

So can someone help me to construct the query to check if the status is closed then use this query

SELECT *, ROUND(DATEDIFF(day,'Open Date',CURRENT_TIMESTAMP))+ " day(s)" as 'Aging' from T1

Else

SELECT *, ROUND(DATEDIFF(day,'Open Date','Closed Date'))+ " day(s)" as 'Aging' from T1

1 answer

2 votes
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 20, 2020

Hi @Shathya Bhama V R ,

You may try the following SQL queries:

Option 1

SELECT *,

CASE WHEN T1.'Status' LIKE "Closed"

THEN ROUND(DATEDIFF(day,'Open Date','Closed Date'))+ " day(s)"

ELSE

ROUND(DATEDIFF(day,'Open Date',CURRENT_TIMESTAMP))+ " day(s)"

END

as 'Aging' from T1

Option 2

SELECT *,

CASE WHEN T1.'Status' LIKE "Open"

THEN ROUND(DATEDIFF(day,'Open Date',CURRENT_TIMESTAMP))+ " day(s)"

WHEN T1.'Status' LIKE "Closed"

THEN ROUND(DATEDIFF(day,'Open Date','Closed Date'))+ " day(s)"

END

as 'Aging' from T1

TT 3.png

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events