How to create a continuous date range given start and end dates using SQL

How to create a continuous date range given start and end dates using SQL

Given the scenario to convert the start and end date of an event to a continuous date range so that  it is easier to plot in TIBCO Spotfire. An 'event A' happened from the 10th of November till the 14th of November with a total loss of 100. When plotting this, you get a single bar at the start of the event showing the total loss. However, you want to see the loss spread out over the entire duration of the event (a loss of 20 every day during the event) as shown below. This would allow you to show all active events on a given day, and not just the events that started on a given day.

Source Table:

EVENT	START	END	Loss
A	11/10/2018	11/14/2018	100
B	11/20/2018	11/21/2018	10

Expected Output Table:

EVENT	START	END	NewDate	LOSS_RESULT
A	11/10/2018	11/14/2018	11/10/2018	20
A	11/10/2018	11/14/2018	11/11/2018	20
A	11/10/2018	11/14/2018	11/12/2018	20
A	11/10/2018	11/14/2018	11/13/2018	20
A	11/10/2018	11/14/2018	11/14/2018	20
B	11/20/2018	11/21/2018	11/20/2018	5
B	11/20/2018	11/21/2018	11/21/2018	5

This can be achieved by modifying the incoming data via the Information link/data connection SQL itself, without any transformations in TIBCO Spotfire Analyst. For example, assuming your data source is Oracle, you can use a SQL query like:

SELECT 
EVENT, 
START, 
END, 
START+LEVEL-1 NewDate, 
LOSS/(END-START+1) LOSS_RESULT 
from EVENTS 
connect by PRIOR EVENT = EVENT 
and level <= (END-START+1)
and PRIOR dbms_random.string ('p', 10)  IS NOT NULL 
order by 1, 2, 3;