Search code examples
sqllooker

Query for kind of pivot of two date column in sql and its count

I have two columns, ticket_created_date and ticket_resolved_date, I want to somehow pivot( if I may call it) them by an SQL query and get something like below image,

  1. the first column is the bucket of the days, i.e, 0 if the difference in 2 dates is less than 1 day,1-2 if difference>1

  2. the next column is the count of tickets, in this case just the row entries, each bucket has.

I am a complete newbie in sql & need this in one of my looker looks for data visualization.

enter image description here

Solution

  • Probably there is a lot of ways to solve this problem, I would use GROUP BY.

    First you will have to get the difference between the 2 dates. Since you didn't specify which DBMS (ORACLE, SQL SERVER, My SQL, etc) you are using I will give examples for oracle and SQL SERVER.

    ORACLE:

    SELECT (ticket_resolved_date - ticket_created_date) AS DAYS FROM TABLE_NAME;

    SQL SERVER:

    SELECT DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) AS DAYS FROM TABLE_NAME;

    if you just group by DAYS and count(*) you will already have the count per day, but you want to create a category ('1-2','3-4',etc). For that you can use CASE.

    ORACLE:

    SELECT CASE 
        WHEN (ticket_resolved_date - ticket_created_date) = 0 THEN 'A - 0'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 1 AND 2 THEN 'B - 1-2'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 3 AND 4 THEN 'C - 3-4'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 5 AND 6 THEN 'D - 5-6'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 7 AND 8 THEN 'E - 7-8'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 9 AND 10 THEN 'F - 9-10'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 11 AND 12 THEN 'G - 11-12'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 13 AND 14 THEN 'H - 13-14'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 15 AND 16 THEN 'I - 15-16'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 17 AND 18 THEN 'J - 17-18'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 19 AND 20 THEN 'K - 19-20'
        ELSE 'L - 20+'
    END AS DAYS_CATEGORY
    ,COUNT(*) AS "#TICKETS"
FROM DATES
GROUP BY CASE 
        WHEN (ticket_resolved_date - ticket_created_date) = 0 THEN 'A - 0'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 1 AND 2 THEN 'B - 1-2'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 3 AND 4 THEN 'C - 3-4'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 5 AND 6 THEN 'D - 5-6'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 7 AND 8 THEN 'E - 7-8'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 9 AND 10 THEN 'F - 9-10'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 11 AND 12 THEN 'G - 11-12'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 13 AND 14 THEN 'H - 13-14'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 15 AND 16 THEN 'I - 15-16'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 17 AND 18 THEN 'J - 17-18'
        WHEN (ticket_resolved_date - ticket_created_date) BETWEEN 19 AND 20 THEN 'K - 19-20'
        ELSE 'L - 20+'
    END
ORDER BY 1;

    SQL SERVER:

    SELECT CASE 
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) = 0 THEN '0'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 1 AND 2 THEN 'A - 1-2'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 3 AND 4 THEN 'B - 3-4'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 5 AND 6 THEN 'C - 5-6'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 7 AND 8 THEN 'D - 7-8'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 9 AND 10 THEN 'E - 9-10'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 11 AND 12 THEN 'F - 11-12'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 13 AND 14 THEN 'G - 13-14'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 15 AND 16 THEN 'H - 15-16'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 17 AND 18 THEN 'I - 17-18'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 19 AND 20 THEN 'J - 19-20'
        ELSE 'K - 20+'
    END AS DAYS_CATEGORY
    ,COUNT(*) AS "#TICKETS"
FROM TEST
GROUP BY CASE
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) = 0 THEN '0'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 1 AND 2 THEN 'A - 1-2'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 3 AND 4 THEN 'B - 3-4'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 5 AND 6 THEN 'C - 5-6'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 7 AND 8 THEN 'D - 7-8'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 9 AND 10 THEN 'E - 9-10'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 11 AND 12 THEN 'F - 11-12'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 13 AND 14 THEN 'G - 13-14'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 15 AND 16 THEN 'H - 15-16'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 17 AND 18 THEN 'I - 17-18'
        WHEN DATEDIFF(DAY,ticket_created_date,ticket_resolved_date) BETWEEN 19 AND 20 THEN 'J - 19-20'
        ELSE 'K - 20+'
    END
ORDER BY 1;