Re: How to Get Every two weeks settlement --allways Wednesday

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 29 Jul 2006 15:13:45 -0700
Message-ID: <1154211225.863046.30490@h48g2000cwc.googlegroups.com>

DA Morgan wrote:
> Charles Hooper wrote:
> > rjayanth_at_gmail.com wrote:
> >> Hi,
> >>
> >> I am trying to get solution for this. I need to get settlement every
> >> two weeks and it alwyas happens on "Wednesday"
> >>
> >> Example :To Day's Date Jul 19
> >>
> >> My output look like this
> >>
> >> Date Day
> >>
> >> Jul19 Wed
> >> Aug 2 Wed
> >> Aug 16 Wed
> >> Aug 30 Wed
> >> ....so on till End Date which is 30 years
> >>
> >> Kindly let me know ASAP
> >>
> >> Oracle Version:Oracle 9i, restriction no PL/SQL
> >>
> >>
> >> Regards
> >> Jay
> >
> > You need a table with at least 780 rows in order to seed a counter,
> > then it is just a matter of multiplying the counter by 14 to hit every
> > other week: DBA_OBJECTS, USER_OBJECTS, etc. For example:
> >
> > SELECT
> > (TO_DATE('2006-07-19', 'YYYY-MM-DD') + (ROWNUM - 1) * 14) NEXT_DATE
> > FROM
> > DBA_OBJECTS
> > WHERE
> > ROWNUM<=780;
> >
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.

>

> Not necessarily. Why not a pipelined table function that could
> be joined in the query (no table and no maintenance ... ever).

>

> There is a demo PTF in Morgan's Library at www.psoug.org that
> shows how one is written and used in a join.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

There is no table to maintain in my example - any table with a sufficient number of rows will work to seed the counter. My example uses the DBA_OBJECTS table, which is not the best choice, but will exist in all Oracle databases (only accessible to users with DBA rights). A smaller table with just enough rows is a better choice. Also, the number 780 (52 / 2 * 30) needs to be adjusted. A year is roughly 52 weeks, 1.24 days, so 783 is probably a better number to use [ CEIL(52 / 2 * YEARS + 1.24 / 14 * YEARS) ]. There is no need for a complicated solution when a simple SQL statement with a little math thrown in accomplishes the task - that is not to say that complicated solutions should not be offered (thanks for the tip on pipelined table functions - I might find a use for that somewhere).

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Jul 29 2006 - 17:13:45 CDT