How to create a continuous date range given start and end dates using SQL
book
Article ID: KB0079973
calendar_today
Updated On:
Products
Versions
Spotfire Analyst
7.5 and higher
Description
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
Resolution
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;
Issue/Introduction
How to create a continuous date range given start and end dates using SQL