Date field sorting issue in email attached report from Analytics

[sunny08]

I have requirement to send the terminated users details with All terms year-to-date,  term date, and sort by most recent date at the top.

I tried with below  sql order by termdate options but when I verified the result in email attachment. It's not sort by most recent date .

SELECT FIRSTNAME, LASTNAME, EMAIL, CASE STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS, DEPARTMENTname as 'DEPARTMENT' ,TiTle as 'Title', Termdate as 'Terminated Date' FROM USERS WHERE Termdate between '2024-01-01' and DATE_SUB(CURDATE(), INTERVAL 0 DAY) AND STATUSKEY = 0 order by termdate desc;

or

SELECT FIRSTNAME, LASTNAME, EMAIL, CASE STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,DEPARTMENTname as 'DEPARTMENT' , termdate ,DATE_FORMAT(Termdate, '%Y-%m-%d')
as 'Terminated Date' , TiTle as 'Title' FROM USERS WHERE Termdate between '2024-01-01' and DATE_SUB(CURDATE(), INTERVAL 0 DAY) AND STATUSKEY = 0 order by DATE_FORMAT(Termdate, '%Y-%m-%d');

[sudeshjaiswal]

Hello @sunny08 ,

This is the expected behavior.  There is already an idea in place for the similar request.

Thanks.

[rushikeshvartak]

Analytics always sort based on first column in report. try below 

SELECT DATE_FORMAT(Termdate, '%Y-%m-%d')
as 'Terminated Date',FIRSTNAME, LASTNAME, EMAIL, CASE STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,DEPARTMENTname as 'DEPARTMENT' , termdate  , TiTle as 'Title' FROM USERS WHERE Termdate between '2024-01-01' and DATE_SUB(CURDATE(), INTERVAL 0 DAY) AND STATUSKEY = 0 order by DATE_FORMAT(Termdate, '%Y-%m-%d');