Couting Problem

[Felgar]

I have a situation where I have a record that has an effective duration, based on days. Ie: There is a start and end date for the active period of my entity. I need a query to generate a set that spans the record over each day that it is active.

For instance, if it is active from Jan 5 to Jan 10, then I need 1 record for each of Jan 5, through to Jan 10.

Ultimately if I could get an Oracle SQL query to return a row, counting from 1 to n, for the n days of the duration I could easily figure out the date. Does anyone know how to return a set of rows, in which a column simply contains a number that grows from 1 to n?

 

[jimbopalmer]

You may wish to look at PL/SQL

for each_julian in to_number(to_char(first_date, J'))..to_number(to_char(second_date, J')) loop
each_date = trunc(to_date(each_julian,'J'));

your stuff

end loop; love everyone til they prove otherwise, sadly some prove otherwise SO quickly

 

[Felgar]

Jimbop:

Yes, this may be the only way to do it. But ultimately I am trying to run a query for a report, which requires a recordset. My front end is delphi, so if I can get the job done using only a query then it's preferable because the dataset component then becomes very simple.

Worst case scenario I could do that loop to populate a temporary table with the proper days, but then I run into table creation and multi-user issues that I'd rather avoid.

I was wondering if I could somehow use connect by?

 

[carp]

Might it be possible to make a table of dates that would include all of the dates you are likely to encounter (e.g., 1-JAN-1999 through 21-DEC-2049 - or whatever)? That way, you could do an outer join between your table of interest and your date table with the WHERE clause including the start and end dates that define your effective duration.
The results would be one row per each day.

 

[Felgar]

Yes Carp, that would work. It would probably be better doing that than trying to clean out the table and re-populate it each time. Even 50 years of days is only 18K records. Plus, it then removes the need for table locking and multi-user considerations, because the table is fixed. And in 2050, I don't really care if the program works or not!