Split date range into new records in Snowflake View

Question

I have a table that consists of start and end dates and I need to need split records into day-wise and this needs to go in a View.

| PersonID  | CompanyID    | Start_DT    | End_DT    |
|-----------|--------------|-------------|-----------|
| A12       | abc          | 05-01-2020  | 05-03-2020|
| B23       | def          | 06-08-2020  | 06-14-2020|

| PersonID  | CompanyID    | New_DT      |
|-----------|--------------|-------------|
| A12       | abc          | 05-01-2020  | ==> A12 Start Date is 05-01-2020 and End Date is 05-03-2020. So there are 3 records generated in New_DT
| A12       | abc          | 05-02-2020  | 
| A12       | abc          | 05-03-2020  | 
| B23       | def          | 06-08-2020  | 
| B23       | def          | 06-09-2020  | 
| B23       | def          | 06-10-2020  | 
| B23       | def          | 06-11-2020  | 
| B23       | def          | 06-12-2020  | 
| B23       | def          | 06-13-2020  | 
| B23       | def          | 06-14-2020  |   

How can I achieve this in View?

Answer 1

You can use a recursive CTE:

with cte as (
      select PersonID, CompanyID, Start_DT as new_dt, End_DT  
      from t
      union all
      select PersonID, CompanyID, dateadd(day, 1, new_dt), End_DT  
      from cte
      where new_dt < end_dt
     )
select PersonID, CompanyID, new_dt
from cte
option (maxrecursion 0);

If you have a period of more than 100 days, then you need to add option (maxrecursion 0).

Here is a db<>fiddle.

Answer 2

To fill in the dates requires a method to generate the rows. One method which is known to be quick and efficient is a tally or numbers table. Something like this

Tally tvf (to generate plenty of rows, enough to fill BIGINT)

drop function if exists [dbo].[fnNumbers];
go
create function [dbo].[fnNumbers](
  @zero_or_one   bit, 
  @n             bigint)
returns table with schemabinding as return
with n(n) as (select null from (values (1),(2),(3),(4)) n(n))
select 0 n where @zero_or_one = 0
union all
select top(@n) row_number() over(order by (select null)) n
from n na, n nb, n nc, n nd, n ne, n nf, n ng, n nh,
     n ni, n nj, n nk, n nl, n nm, n np, n nq, n nr;
go

Query

select it.PersonID, it.CompanyID, da.dt
from #t it
     cross apply
     dbo.fnNumbers(0, datediff(day, Start_DT, End_DT)) fn
     cross apply
     (select dateadd(day, fn.n, it.Start_DT) dt) da;

Output

PersonID    CompanyID   dt
A12         abc         2020-05-01
A12         abc         2020-05-02
A12         abc         2020-05-03
B23         def         2020-06-08
B23         def         2020-06-09
B23         def         2020-06-10
B23         def         2020-06-11
B23         def         2020-06-12
B23         def         2020-06-13
B23         def         2020-06-14