CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
Jérémie
Jérémie

Reputation: 39

Create rows of intermediary dates

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!

Upvotes: 2

Views: 70

Answers (1)

Pavel Slepiankou
Pavel Slepiankou

Reputation: 3585

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

Upvotes: 3

Related Questions