SQL - How to group/count items by age and status on every date of a year?

user_13644825 0
Viewed 245

I am trying to build a query from multi-year data set (tickets table) of support tickets, with relevant columns of ticked_id, status, created_on date and closed_on date for each ticket. There is also a generic dates table I can join/query to a list of dates.

I'd like to create a "burn down" chart for this year that displays the number of open tickets that were at least a year old on any given date this year. I have been able to create tables that use a sum(case... statement to group by a date - for example to show how many tickets were created on a given week - but I can't figure out how to group by every day or week this year the number of tickets that were open on that day and at least a year old.

Any help is appreciated.

Example Data:

ticket_id | status | created_on | closed_on
--------------------------------------------
 1          open     1/5/2019     
 2          open     1/26/2019
 3          closed   1/28/2019    2/1/2020
 4          open     6/1/2019
 5          closed   6/5/2019     1/1/2020

Example Results I Seek:

Date (2020)    |     Count of Year+ Aged Tickets
------------------------------------------------
1/1/2020             0
1/2/2020             0
1/3/2020             0
1/4/2020             0
1/5/2020             1
1/6/2020             1
... (skipping dates here but want all dates in results)...
1/25/2020            1
1/26/2020            2
1/27/2020            2
1/28/2020            3
1/29/2020            3
1/30/2020            3
1/31/2020            3
2/1/2020             2
... (skipping dates here but want all dates up to current date in results)...
  • ticket_id 1 reached one year of age on 1/5/2020 and is still open (remains in count)
  • ticket_id 2 reached one year of age on 1/26/2020 and is still open (remains in count)
  • ticket_id 3 reached one year of age on 1/28/2020 and was still open, adding to the count, but was closed on 2/1/2020, reducing the count
  • ticket_id 4 will only add to the count if it is still open on 6/1/2020, but not if it is closed before then
  • ticket_id 5 will never appear in the count because it never reached one year of age and is closed
amazon-redshift recursive-query join date sql
2 Answers

One option is to build a sequential list of dates, then bring the table with a ‘left join` and conditional logic, and finally aggregate.

This would give the results you want for year 2020.

select d.dt, count(t.ticket_id) no_tickets
from (
    select date '2020-01-01' + I * interval '1 day' dt 
    from generate_series(0, 365) i 
) d
left join mytable t
    on t.created_on + interval '1 year' <= d.dt 
    and (
        t.closed_on is null 
        or t.closed_on > d.dt
    )
group by d.dt

If your version of Redshift does not support generate_series(), you can emulate it a custom number table, or with row_number() against a large table (say mylargetable):

select d.dt, count(t.ticket_id) no_tickets
from (
    select date '2020-01-01' + row_number() over(order by 1) * interval '1 day' dt 
    from mylargetable 
) d
left join mytable t
    on t.created_on + interval '1 year' <= d.dt 
    and (
        t.closed_on is null 
        or t.closed_on > d.dt
    )
where d.dt < date '2021-01-01'
group by d.dt
user_106767160

If ticket_id is unique then you can do this to get all ticket at least 1 year old

select ticket_id, created_on , status where status = 'open' and created_on <= dateadd(year,-1,getdate())

if you want to count number of ticket per month then 

select count(ticket_id), month(created_on) , status where status = 'open' and created_on <= dateadd(year,-1,getdate())
group by month(created_on)
user_26355660
Related