I am using confluence to track the risks and the table has these columns:
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.