How to Get Every two weeks settlement --allways Wednesday

#11

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> wrote

Quote:

DA Morgan wrote:
Charles Hooper wrote:
rjayanth (AT) gmail (DOT) 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@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..

#12

tony_becky_mikey_verizon_news schreef:

Quote:

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

You're barking up the wrong tree, Tony. Charles did not post

the question, nor the restriction.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

#13

tony_becky_mikey_verizon_news wrote:

Quote:

"Charles Hooper" <hooperc2000 (AT) yahoo (DOT) com> wrote in message
news:1154211225.863046.30490 (AT) h48g2000cwc (DOT) googlegroups.com...
DA Morgan wrote:
Charles Hooper wrote:
rjayanth (AT) gmail (DOT) 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@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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

rjayanth · #14

I agree frank openion on PL/SQL, Since my problem is we need this in a report, and we have restriction on using PL/SQL.

Thanks a lot for the reply,

Regards
Jay

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to Get Every two weeks settlement --allways Wednesday

comp.databases.oracle.misc comp.databases.oracle.misc

Re: How to Get Every two weeks settlement --allways Wednesday - 07-29-2006 , 11:05 PM

Re: How to Get Every two weeks settlement --allways Wednesday - 07-30-2006 , 04:17 AM

Re: How to Get Every two weeks settlement --allways Wednesday - 07-30-2006 , 11:40 AM

08-05-2006 , 06:53 AM