I still feel that
to_char(add_months(sysdate, 6), 'Q')
is a simpler solution than
MOD(TO_NUMBER(TO_CHAR(add_months(sysdate,9),'Q'))+1,4) + 1
when determining the fiscal quarter. Also, the latter solution above is
off-by-one regarding the results per your original email wherein the fiscal
year starts with Jul through Sep as the first quarter. To wit:
SQL> set serveroutput on
SQL> exec dbms_output.enable;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
SQL> declare
2 d date;
3 d2 date;
4 q1 number;
5 q2 number;
6 n number;
7 begin
8 d := to_date('1-Jan-2001', 'dd-Mon-yyyy');
9 for n in 0 .. 11
10 loop
11 d2 := add_months(d, n);
12 q1 := mod(to_number(to_char(add_months(d2, 9),'Q'))+1,4) + 1;
13 q2 := to_char(add_months(d2, 6), 'Q');
14 dbms_output.put_line(
15 to_char(d2, 'dd-Mon-yyyy') || ': ' ||
16 to_char(q1) || ' ' ||
17 to_char(q2));
18 end loop;
19 end;
20 /
01-Jan-2001: 2 3
01-Feb-2001: 2 3
01-Mar-2001: 2 3
01-Apr-2001: 3 4
01-May-2001: 3 4
01-Jun-2001: 3 4
01-Jul-2001: 4 1
01-Aug-2001: 4 1
01-Sep-2001: 4 1
01-Oct-2001: 1 2
01-Nov-2001: 1 2
01-Dec-2001: 1 2
PL/SQL procedure successfully completed.
Don't get locked into the whole adding 9 months bit, which as I've pointed
out in my earlier email is not a correct offset. Having now added the
incorrect 9 months, it looks like you're trying to make corrections to the
skewed results by adding 1 then modding by 4, and finally adding another one
because you will get a zero out of the mod operation when in the fourth
quarter. Your fiscal year happens to lag the calendar year by 6 months, so
just add 6 months to the date for the to_char() operation.
Regards.
...Rudy
-----Original Message-----
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 02, 2003 4:06 PM
To: Multiple recipients of list ORACLE-L
Hi Rudy,
Thanks for your suggestion. But I got a better suggestion from Metalink.
Here what I was suggested:
SQL> SELECT (MOD(TO_NUMBER(TO_CHAR(add_months(sysdate,9),'Q'))+1,4) + 1) qtr
2 , DECODE(SIGN(TO_NUMBER(TO_CHAR(main_rec.termination_date,'Q')) - 3)
3 , -1, TO_NUMBER(TO_CHAR(main_rec.termination_date,'YYYY'))
4 , TO_NUMBER(TO_CHAR(main_rec.termination_date,'YYYY')) + 1) yr
5 ...
6
Thanks for your help.
Surendra
-----Original Message-----
Sent: Wednesday, July 02, 2003 1:51 PM
To: Multiple recipients of list ORACLE-L
If your Jul is 1st quarter, then your offset is should be 6 months instead
of the 9 months in your email; or think of it another way, if you Jan is the
beginning of the 3rd quarter, it is the beginning of the 2nd half of the
year, and half a year is 6 months.
With this in mind, you really don't need any decodes at all for just finding
out your financial quarter. The following query suffices:
select to_char(add_months(sysdate,
6),
'Q') from user_users;
My best interpretation of your second query with the if-then is that if a
date is in the second half of the calendar, you want to push it to the
following year (or perhaps if the calendar date is from second half of last
year, you want it reported as being in this year, which is kind of like
saying fiscal year ending xxxx)
So, here's your solution (not exactly fast, but it's implemented competely
with numeric functions provided by Oracle):
Let's make Q = to_number(to_char(add_months(sysdate, 6), 'Q'))
To figure out how much correction you need to each year based on the quarter
the year appears in, add the following to the year:
sign((sign(3 - Q) + 1) * sign(3 - Q))
-----Original Message-----
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 02, 2003 11:49 AM
To: Multiple recipients of list ORACLE-L
Hello ALL,
I am trying to find quarter number from a given date . Here is the
description
Our Financial year runs from July thru June. So, Given any date between
these dates I need to find 3 quarters(9 months) from it.
July -Sep -> 1st Quarter
Oct -Dec -> 2nd Quarter
Jan -Mar -> 3rd Quarter
Apr -Jun -> 4th Quarter
I got this done using the following Select
select decode(to_char(add_months(sysdate,9),'Q'),
'1','3',
'2','4',
'3','1',
'4','2',
'')
Decode function is used to change calender quarter to our Quarter.
But I am unable to find the Year for that quarter.
I was using Case statement to solve my problem,as below
Select case when decode(to_char(add_months(sysdate,9),'Q'),
'1','3',
'2','4',
'3','1',
'4','2',
'') < 3
then to_number(to_char(main_rec.termination_date,''YYYY''))+1
else to_number(to_char(main_rec.termination_date,''YYYY''))
P.S The reason for <3 condition check in CASE Statement is, if a
sysdate+9months falls in next Financial year , I need to change Year
accordingly.
But,this works only in SQL, in Procedures, i cannot do this using CASE
Statement
Can anybody give some ideas on how to approach this?. I have to use this in
a cursor (not in the body of my procedure, so condition checking like "If
then else " after fetching year is not possible)
Any help would be greatly appreciated.
Thanks,
Surendra Tirumala
Database Administrator
Cabinet for Workforce Development
Commonwealth of Kentucky
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rudy Zung
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rudy Zung
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
