CODEWITHSUNDEEP
sqlpresto
Emma
Emma

Reputation: 403

Add rows between two dates Presto

I have a table that has 3 columns- start, end and emp_num. I want to generate a new table which has all dates between these dates for every employee. Need to use Presto.

I refered this link - inserting dates into a table between a start and end date in Presto

Tried using unnest function by creating sequence but , I don't know how do I create sequence by pulling dates from two columns in another table.

select unnest(seq) as t(days)
from (select sequence(start, end, interval '1' day) as seq 
      from table1)

Here's table and expected format

Table 1:
start       |  end         | emp_num 
2018/01/01  |   2018/01/05 | 1
2019/02/01  |   2019/02/05 | 2


Expected:
start          | emp_num 
2018/01/01     | 1
2018/01/02     | 1
2018/01/03     | 1
2018/01/04     | 1
2018/01/05     | 1
2019/02/01     | 2
2019/01/02     | 2
2019/02/03     | 2
2019/02/04     | 2
2019/02/05     | 2

Upvotes: 4

Views: 4642

Answers (1)

GMB
GMB

Reputation: 222482

Here is a query that might get the job done for your use case.

The logic is to use Presto sequence() function to generate a wide date range (since year 2000 to end of 2018, you can adapt that as needed), that can be joined with the table to generate the output.

select dt.x, emp_num
from 
    ( select x from unnest(sequence(date '2000-01-01', date '2018-01-31')) t(x) ) dt
    inner join table1 ta on dt.x >= ta.start and dt.x <= ta.end

However, as commented JNevill, it would be more efficient to create a calendar table rather than generating it on the fly every time the query runs.

It should be a simple as :

create table calendar as
    select x from unnest(sequence(date '1970-01-01', date '2099-01-01')) t(x);

And then your query would become :

select dt.x, emp_num
from 
    calendar dt
    inner join table1 ta on dt.x >= ta.start and dt.x <= ta.end

PS : due to the lack of DB Fiddles for Presto in the wild, I could not test the queries (@PiotrFindeisen - if you happen to read this - a Presto fiddle would be nice to have !).

Upvotes: 4

Related Questions