Table Transformer Macro - If else condition

Shathya Bhama V R

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

Katerina Kovriga {Stiltsoft}

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