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