Create rows of intermediary dates

Question

I'm looking to get the number of tickets that are unassigned at the end of each day over a specified period of days using historical data. I'm using Amazon Redshift.

The query currently has this structure:

ticket_id	created_at	assigned_at
1	2020-11-18	2020-11-20
2	2020-11-18	2020-11-18
3	2020-11-17	2020-11-20

My current strategy is to use the created_at date, create a new row with the created_date + 1 day until it is the same as the assigned_at date. From there I can easily manipulate the output with Tableau.

The output that I'm looking for is:

ticket_id	created_at	assigned_at
1	2020-11-18	2020-11-20
1	2020-11-19	2020-11-20
1	2020-11-20	2020-11-20
2	2020-11-18	2020-11-18
3	2020-11-17	2020-11-20
3	2020-11-18	2020-11-20
3	2020-11-19	2020-11-20
3	2020-11-20	2020-11-20

In the end I want to be able to get to this:

date	tickets in queue
2020-11-17	1
2020-11-18	2
2020-11-19	2
2020-11-20	0

I'm also open to other suggestions regarding how to solve this problem.

Thanks in advance!

user_798335 · Answer

You can create an additional table with calendar

create table calendar (
    calendar_date date
);

calendar_date
2020-11-17
2020-11-18
2020-11-19
2020-11-20

then join against it

select calendar_date, count(*) 
from table_name as t 
    left join calendar as c on t.created_at <= c.calendar_date 
                            and c.calendar_date < t.assigned_at 
where c.calendar_date <> t.assigned_at 
group by 1
order by 1;

it gives the following output

calendar_date	count
2020-11-17	1
2020-11-18	2
2020-11-19	2

2020-11-20 is absent in this result as there's no ticket

You can get it with a bit complex query

with calendar_gr as (
    select calendar_date, 0 as cnt
    from calendar
),
r1 as (
    select calendar_date, count(*)  
    from table_name as t 
        left join calendar as c on t.created_at <= c.calendar_date 
                                and c.calendar_date < t.assigned_at                         
    where calendar_date <> assigned_at 
    group by 1
    order by 1
)
select calendar_gr.calendar_date, sum(coalesce(count, 0) + coalesce(cnt, 0)) 
from r1
    right join calendar_gr on r1.calendar_date = calendar_gr.calendar_date
group by 1
;

calendar_date	count
2020-11-17	1
2020-11-18	2
2020-11-19	2
2020-11-20	0

Create rows of intermediary dates

1 Answers