Home » SQL & PL/SQL » SQL & PL/SQL » Extracting Months (oracle 10g)
Extracting Months [message #393872] Tue, 24 March 2009 15:03 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

We have a requirement to load data from a table based on a Date column,

Basically to load data for Previous Financial Year, and at our place Financial Year is 12/1 To 11/30.

Let's say we are in May 2009, And on every 2 Day of the month i will run this procedure to load, based on current month , In this case now for eg :-
it will take the current month and decide how far back it should load based on sales_date from source table,

May 2009 Going back to 12/1/07 , Since Previous financial Year was from 12/1/07 To 11/30/09 Plus the months after that till we reach the current financial Year,

And if we reach 12/1/09 at that time it will load only from 12/1/08 To 11/30/08.

How can i acheive this, either multiple selects or a function,

Hope i explained my requirements.

Please guide me on this.

Thanks
Re: Extracting Months [message #393874 is a reply to message #393872] Tue, 24 March 2009 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You will not get answers unless * until you follow Posting Guidelines!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Extracting Months [message #393877 is a reply to message #393872] Tue, 24 March 2009 15:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Extracting Months [message #393902 is a reply to message #393877] Tue, 24 March 2009 19:12 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for your time,
Please find the DDL and DML for the source and Target tables.
[U][B]SOURCE TABLE[/B][/U]

CREATE TABLE EXCHANGE_RATES
(
  CURR_CODE_DESC          VARCHAR2(25 BYTE),
  RATE                    FLOAT(126),
  EXCH_DATE               DATE        NOT NULL);

[U][B]INSERT STATEMENT FOR SOURCE TABLE[/B][/U]
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('IRR', 0.79,TO_DATE('01/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('ILS', 0.95,TO_DATE('12/01/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('EUR', 0.79,TO_DATE('03/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('CAD', 0.95,TO_DATE('02/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('AED', 0.79,TO_DATE('03/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('SAR', 0.95,TO_DATE('04/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('OMR', 0.79,TO_DATE('05/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('USD', 1.00,TO_DATE('06/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('IRR', 0.79,TO_DATE('07/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('ILS', 0.95,TO_DATE('08/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('IRR', 0.79,TO_DATE('09/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('ILS', 0.95,TO_DATE('10/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('IRR', 0.79,TO_DATE('11/30/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EXCHANGE_RATES    (CURR_CODE, EXCH_DATE)  Values ('ILS', 0.95,TO_DATE('10/01/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));


[U][B] TARGET TABLE[/B][/U]

CREATE TABLE CURRENCY_RATES
(
  CURR_CODE_DESC          VARCHAR2(25 BYTE),
  RATE                    FLOAT(126),
  EXCH_DATE               DATE        NOT NULL);



IF i run the job now (03/09) to insert into the target table, it should load all the records from the last financial year (12/07 To Till Date) based on EXCH_DATE of the source table.
And if i run the same in DEC 09 (12/09), It should load the records from 12/1/08 To till date.

If you need some more info, Please let me know i will get it.

Thanks All for your time.

Oracle Version is 10.2.0.3

Thanks
Re: Extracting Months [message #393939 is a reply to message #393902] Wed, 25 March 2009 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHat is the rule that define the starting date?

Regards
Michel
Re: Extracting Months [message #394083 is a reply to message #393939] Wed, 25 March 2009 12:00 Go to previous message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Michel for your time,

Starting date is , Like Every Second of the Month , I will run the Procedure to Load from Source to Target.

Hope i answered your questions

Thanks
Previous Topic: Function in predicate slowing query
Next Topic: How to track/find which transaction is consuming much more time?
Goto Forum:
  


Current Time: Wed Dec 07 22:30:13 CST 2016

Total time taken to generate the page: 0.14535 seconds