In this post, we will see how to resolve SQL: Learning About Gap-And-Island Problems

## Question:

I am working with Netezza SQL.In a previous question (Replacing CTE’s with Individual Queries) I learned about the basics of “Gap and Island Problems” in which the goal is to “fill” missing records for each name.

Suppose there is a table with the names of different people over different years (some of the years are missing). Lets assume that each person has favorite color, favorite food and favorite sport – and this information DOES NOT change over the years. However, the age of each person DOES change each year.

**In this problem, I am interested in filling missing information (e.g. age, sport, food, color) only between the min and the max year for each person.**Specifically, I would like to learn how to do this problem without CTEs and through a “standard query”.

Here is what I have attempted so far:

**But I am not sure how to adapt this SQL code to make the “age” information for each person change over the years.**

Can someone please show me how to do this? Preferably, I would like to learn how to do this without Recursive CTE’s as they are not supported in Netezza.

Thanks!

**Note:**The final result should look something like this:

## Best Answer:

I do not know about Netezza so much but if it supports window functions, you can use a`LEAD`

to get the year of the next record in your table.If you still wish to get the expected table you mention right out of the database, simply join with

`years_table`

, although it needs to start on year 2000.`years_table`

with something in the line of what is presented here (I must admit it is slightly above what I know of that DBMS and I have no access to one to test):If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com