Recursive Table in MV

[nikhil_cr]

Hi,

I have start date (File Date) and end date in the table and i want to repeat the rows for each day from start date to end date. Is there any way that I can achieve this in MV? 

Please see screenshot - 

Appreciate your help in Advance. Thanks.

[anurag]

What is your use case? How will this be used?

what will your output look like?  First column will be date, what about other columns?

[nikhil_cr]

Hi @anurag,

I'm trying to pull the forecast details on a daily basis. I have this start date (File date) and end date, once I get the list of Rows between these two dates for each day - I can map them with my daily forecast table. I'm trying to achieve something similar to this - https://stackoverflow.com/questions/50380904/how-to-duplicate-records-according-to-start-and-end-dat...

But it seems the recursive CTE logic is not working in MV. Can you please help me with some alternate method? 

My output will look something like this - (Red being the start date and green being the end date)

Thanks,

Nikhil 

[anurag · Accepted solution]

@nikhil_cr 

Sample SQL to do this using MV:
SELECT explode(sequence(orderdate, shipdate, interval 1 day)) as date, Total_Sales FROM order_ship_date_query_result

Checkout this video as well:

https://youtu.be/u0GrrnK2ZHA

[nikhil_cr]

@anurag  Thank you so much. It is working as expected.