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.

sheldonlg wrote:
>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.

Sounds like a job for a self join, but what happens to the "last" row?

On Wed, 21 May 2008 19:49:38 +0200, sheldonlg <sheldonlg> wrote:

> 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.

So, use the YEAR() and MONTH() functions instead of other fields.

> 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.


You shouldn't need the enddate, it can be queries simply from the table,
so storing it in a field is in almost all cases wasted effort. However, if
you want to do it, here it is:

Considering a table:

tablename
FP_YEAR INT NOT NULL
FP_MONTH INT NOT NULL
FP_STDT DATETIME NOT NULL

ALTER TABLE tablename ADD COLUMN FP_ENDDT DATETIME NULL DEFAULT NULL;

UPDATE tablename updatedate
JOIN tablename newdate
ON newdate.FP_STDT > updatedate.FP_STDT
LEFT JOIN tablename betweendate
ON betweendate.FP_STDT < newdate.FP_STDT
AND betweendate.FP_STDT > updatedate.FP_STDT
SET updatedate.FP_ENDDT = newdate.FP_STDT
WHERE betweendate.FP_STDT IS NULL;

Which will leave you, if all is well, with one record (or possibbly
multiples if FP_STDT is not unique) having FP_ENDDT NULL because there is
no newer date known in the table.
--
Rik Wasmus
....spamrun finished

Rik Wasmus wrote:
> On Wed, 21 May 2008 19:49:38 +0200, sheldonlg <sheldonlg> wrote:
>
>> 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.
>
> So, use the YEAR() and MONTH() functions instead of other fields.
>
>> 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.
>
>
> You shouldn't need the enddate, it can be queries simply from the table,
> so storing it in a field is in almost all cases wasted effort. However,
> if you want to do it, here it is:
>
> Considering a table:
>
> tablename
> FP_YEAR INT NOT NULL
> FP_MONTH INT NOT NULL
> FP_STDT DATETIME NOT NULL
>
> ALTER TABLE tablename ADD COLUMN FP_ENDDT DATETIME NULL DEFAULT NULL;
>
> UPDATE tablename updatedate
> JOIN tablename newdate
> ON newdate.FP_STDT > updatedate.FP_STDT
> LEFT JOIN tablename betweendate
> ON betweendate.FP_STDT < newdate.FP_STDT
> AND betweendate.FP_STDT > updatedate.FP_STDT
> SET updatedate.FP_ENDDT = newdate.FP_STDT
> WHERE betweendate.FP_STDT IS NULL;
>
> Which will leave you, if all is well, with one record (or possibbly
> multiples if FP_STDT is not unique) having FP_ENDDT NULL because there
> is no newer date known in the table.

Thanks. I just noticed that I put in an error in the original post. It
wasn't FP_MONTH. It was FP_PRNO, and that didn't necessarily correspond
to a month.

Wow, what a query!

I would try it, but I realized after posting this here that I put it
into the wrong newsgroup. I am so used to using mysql databases, that I
didn't think and automatically went here. The db is actually and Oracle
db and so I posted it there later. They were quick with a response that
there is a LAG function that does just what I want it to do. See:

http://download-uk.oracle.com/docs/c...0.htm#i1327527

Out of curiousity, does mysql have that function as well.

Thanks again for the help Rik (and Paul).

On Thu, 22 May 2008 00:36:09 +0200, sheldonlg <sheldonlg> wrote:

> Rik Wasmus wrote:
>> On Wed, 21 May 2008 19:49:38 +0200, sheldonlg <sheldonlg> wrote:
>>
>>> 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.
>> So, use the YEAR() and MONTH() functions instead of other fields.
>>
>>> 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.
>> You shouldn't need the enddate, it can be queries simply from the
>> table, so storing it in a field is in almost all cases wasted effort.
>> However, if you want to do it, here it is:
>> Considering a table:
>> tablename
>> FP_YEAR INT NOT NULL
>> FP_MONTH INT NOT NULL
>> FP_STDT DATETIME NOT NULL
>> ALTER TABLE tablename ADD COLUMN FP_ENDDT DATETIME NULL DEFAULT NULL;
>> UPDATE tablename updatedate
>> JOIN tablename newdate
>> ON newdate.FP_STDT > updatedate.FP_STDT
>> LEFT JOIN tablename betweendate
>> ON betweendate.FP_STDT < newdate.FP_STDT
>> AND betweendate.FP_STDT > updatedate.FP_STDT
>> SET updatedate.FP_ENDDT = newdate.FP_STDT
>> WHERE betweendate.FP_STDT IS NULL;
>> Which will leave you, if all is well, with one record (or possibbly
>> multiples if FP_STDT is not unique) having FP_ENDDT NULL because there
>> is no newer date known in the table.
>
> Thanks. I just noticed that I put in an error in the original post. It
> wasn't FP_MONTH. It was FP_PRNO, and that didn't necessarily correspond
> to a month.
>
> Wow, what a query!
>
> I would try it, but I realized after posting this here that I put it
> into the wrong newsgroup. I am so used to using mysql databases, thatI
> didn't think and automatically went here. The db is actually and Oracle
> db and so I posted it there later. They were quick with a response that
> there is a LAG function that does just what I want it to do. See:
>
> http://download-uk.oracle.com/docs/c...0.htm#i1327527
>
> Out of curiousity, does mysql have that function as well.

Nope, but the query would also work in Oracle AFAIK :P
--
Rik Wasmus
....spamrun finished