How to create a continuous date range given start and end dates using SQL
Article ID: KB0079973
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:
Expected Output Table:
Source Table:
| EVENT | START | END | Loss |
| A | 11/10/2018 | 11/14/2018 | 100 |
| B | 11/20/2018 | 11/21/2018 | 10 |
| 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 |
Issue/Introduction
How to create a continuous date range given start and end dates using SQL
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;
Feedback
Yes
No
Powered by
