Is this possible in sql?

From: <sheldonlg>
Date: Wed, 21 May 2008 16:51:13 -0400
Message-ID: <>

I posted this in the mysql newsgroup before finding this one. My db is oracle. So, here it is:

I have a table like this: FP_YEAR, FP_MONTH, FP_STDT (with other things in it as well) where FP means fiscal period and STDT is the start date that goes with the year and month. Yes, it is redundant, but this table has to interact with others where some are on year and month and the others are on start date.

I want to add FP_ENDDT where ENDDT is either the last day before the next period or the beginning of the next period. I don't really care which, since I will either use "<" or "<=" for the date range.

This table is built from another one that the three fields above. What I want to know is whether it is possible, using that reference table, to build my table and have ENDDT populated by looking at the next (or previous) row. All my sql experience has been with a single row or from the entire table. I don't know if it is possible to reference the adjacent row.

I can pull the table out, and in php manufacture the row by doing array manipulations. Putting it back, though, would require "n" calls unless there is a way to update successive rows in a table, each with its own FP_YEAR and FP_MONTH as where clause restrictions.

So, it is possible to do what I want strictly in SQL, or will I have to go to multiple calls. Of course, I can restrict the number of return calls by only putting into the array where FP_ENDDT is null. That would mean I would only have the large number of calls the first time. Each time I add a new period I would then have one (or two) calls. However, if it is possible in SQL, I would prefer that way. Received on Wed May 21 2008 - 15:51:13 CDT

Original text of this message