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

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 30 Jul 2006 09:40:59 -0700
Message-ID: <1154277659.677872@bubbleator.drizzle.com>

tony_becky_mikey_verizon_news wrote:

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

Perhaps he did it because (A) it meets the original criterion of a SQL solution and (B) because it is efficient, and (C) because it works.

What solution did you offer to help the OP? I seem to have missed it.

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

Received on Sun Jul 30 2006 - 11:40:59 CDT