I have a set of data (32m records) that has a start and end years each record.
Using this for any type of calculation is very slow and very complicated. We need to know if a particular record feels in a particular year.
For this, we are trying to build a table that has a row that contain the key fields and a year. That year would be all the years between start and end. so if a particular records goes from 2003 to 2012, we will have a table that has
We alredy try two ways to do this, and all of them works. But they take more than 6 hours to run (a lot more).
One of them goes all the table all the time adding one year at a time for each key.
the other has all the diference between start and end, builds a table that has all the years, and then concatenate it to the original table. This works better than the other one, but takes around a minute for each start-end row (and they are about 3500 in a select disticnt)...
So any one has a better idea on how to build this table?