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

From: tony_becky_mikey_verizon_news <tony_becky_mikey_at_verizon.net>
Date: Sun, 30 Jul 2006 04:05:36 GMT
Message-ID: <keWyg.5254$c11.4989@trnddc08>

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1154211225.863046.30490_at_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.

>

Then why in the bloody hell did you post this to a group, with such whacked out conditions? Not using pl/sql in oracle is liking driving a car with no steering wheel.. Received on Sat Jul 29 2006 - 23:05:36 CDT