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?
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.