How do I have sorted column names based on a condition?

Question

I have a table which has the following columns-

• Incident ID (varchar)
• Created Date (varchar- eg- '01-OCT-2014')
• Status (varchar- eg- 'open','closed')

I need to count the incidents based on status and range of dates starting from the created date.

eg.

Date Range       || Open          || Closed ||
1-2 days         ||         5     ||       6 ||
2-5 days         ||         4     ||       8 ||
5-15 days        ||         10    ||       15 ||

Any help would be appreciated, thanks.

Answer 1

SELECT CASE WHEN DATEDIFF(created_date, now()) BETWEEN 1 AND 2
                THEN '1-2 days'
            WHEN DATEDIFF(created_date, now()) BETWEEN 3 AND 5
                THEN '3-5 days'
            WHEN DATEDIFF(created_date, now()) BETWEEN 6 AND 15
                THEN '6-15 days'
            ELSE '> 15 days'
        END AS DateRange,
        SUM(CASE WHEN Status = 'open' THEN 1 ELSE 0 END) AS Open,
        SUM(CASE WHEN Status = 'closed' THEN 1 ELSE 0 END) AS Closed
FROM YourTable
GROUP BY DateRange