How do I generate a continuous data sheet from periodic records?

Toad Data Point
1

I am not even sure if I am asking the question properly. I imagine that this is just me going outside my " SQL comfort zone." I am using Toad 4.1 with an Oracle database.

The issue I am trying to address is to take data that is periodic in nature and transform it so that I have a continuous list of data that includes that unit’s status for each period of time. Specifically, my data looks somewhat like this:

UNIT
TERM CODE
STARTING TERM
STATUS
1
201201
201201
R
1
201303
201201
N
1
201401
201201
R
1
201601
201201
N

What I want to do is create a list of records that show that unit’s status for each term code between the unit’s starting term and now. Assuming that “now” is 201703, then I want the results to look like this:

UNIT
TERM CODE
STATUS
1

201201
R
1
201202
R
1
201203
R
1
201301
R
1
201302
R
1
201303
N
1
201401
R
1
201402
R
1
201403
R
1
201501
R
1
201502
R
1
201503
R
1
201601
N
1
201602
N
1
201603
N
1
201701
N
1
201702
N
1
201703
N

The goal of this is to be able to link this result set to other queries where a term code can return the applicable status code for that specific term.

Any guidance is appreciated!

2

Ugh. The formatting on the actual post is just brutal. Sorry about that… !

3

SELECT *

FROM <table_name>

ORDER BY UNIT, TERM_CODE

4

Thanks for the reply! Unfortunately, that will essentially give me the starting data set.

I need to be able to fill in the gaps so that I see not only when the status value changes, but the value during each of the intervening terms.

5

Ah ha…in that case, I will need to think a little more. I have a couple of ideas and will do some testing. Is there another table that is the master data for the TERM_CODE…it would contain every term_code?

6

Thanks, and yes, there is a validation table for the term codes that contains the code, its description, activity date, and other related information such as start, end, etc.

7

If I understand you correctly, you need to fill in each period in the range from start date to today? each period being a month? You may need to Join to a table (inline, temp or real table) that contains all the months. Join on month >= startMonth