Thank you Tom for your Timely help!! :-)

Tom, thanks for the response, but what if my Fiscal Calendar Starts on November 1st?

Tom,
I need to generate backups (RMAN) with different retentions at the end of every Fiscal Month End (FME) and each fiscal year end. The way I propose to accomplish this is - first identify, if sysdate=FME. If yes, generate a backup with a different unique tag. And then a "delete database backup tag=’FME’ older than SYSDATE-365" RMAN job will delete the FME backups depending on the retention requirements (1 year). I intend using a similar concept for fiscal year end backups as well.

First things first - I'm not sure how to go about identifying the fiscal month end (especially during a leap year - sometimes, that too!). For example, we 'typically' follow a 4+4+5 pattern, but not always. This year (2004) would be an exception to this rule when Q4 would follow a 4+5+5 pattern.

My month ends (all saturdays) for this year are 24-JAN-2004,21-FEB,27-MAR,24-APR,22-MAY,26-JUN-2004,24-JUL,21-AUG,25-SEP,23-OCT,27-NOV,01-JAN-2005! Translates to 4-4-5, 4-4-5, 4-4-5, 4-"5"-5 pattern. Notice how the fiscal year end for 2004 is actually 01-JAN-2005!

Year 1996 on the other hand, another leap year, started with 27-JAN-1996 and ended with 28-DEC-1996. Thus following a 4-4-5 consistently!)

Fiscal month ends for 2003 looked like this -
25-JAN-2003,22-FEB,29-MAR,26-APR,24-MAY,28-JUN,26-JUL,23-AUG,27-SEP,25-OCT,22-NOV,28-DEC-2003. It followed a 4-4-5 pattern consistently as well.

Do you have any suggestions on how I can generate these dates intelligently without having to hardcode values in a table?

I'm not asking you write code for me! It'll suffice if you could guide me on 'how to fish' :). I’m having a hard time trying to generate these fiscal month ends programmatically.

Thanks for your time.

I've tried to put it across as well as I can. Let me try again.

<QUOTE>
My month ends (all saturdays) for this year are
24-JAN-2004,21-FEB,27-MAR,24-APR,22-MAY,26-JUN-2004,24-JUL,21-AUG,25-SEP,23-OCT,27-NOV,01-JAN-2005! Translates to 4-4-5, 4-4-5, 4-4-5, 4-"5"-5 pattern.
</QUOTE>

Lets start with months of Jan, Feb, March of 2004. A "4-4-5" in quarter 1 of 2004 translates to "4th Sat in Jan which is 24-Jan-2004. Move 4 saturdays from there. You arrive at 21-Feb-2004. Move 5 saturdays from there and you are now at 27-Mar-2004". Each of the above 3 dates were my Fiscal month ends for Jan,Feb,Mar respectively. If you looked at the calendar for the above three months, it might help you figure what I'm saying.

Hope that helps somewhat.

Back to the original date component issue, sort of.

9i introduced the Extract() datetime function, to perform such magic as EXTRACT(MONTH FROM my_date), and it doesn't seem to be widely referenced in the documentation outside of the SQL Reference.

I'm wondering whether there is any advantage in adopting this syntax over TO_CHAR(my_date,'MM') -- does the optimizer have any special understanding of the nature of the EXTRACT() function does in comparison to TO_CHAR()? I've run a few small comparisons of how the optimizer estimates the number of rows to be returned by an expression such as ...

SELECT * FROM MY_TABLE WHERE EXTRACT(MONTH FROM MY_DATE) = '12';

... compared to ...

SELECT * FROM MY_TABLE WHERE TO_CHAR(MY_DATE,'MM') = '12';

... and found no difference.

Is EXTRACT just a wrapper for TO_CHAR()? Is there an ANSI compatibility issue being solved by it's introduction, or something?

To "A reader" ... in case you still want some opinions, you need to clarify few things.

So, you want an algorithm to identify all FMEs for any given year (leap or otherwise … why does it matter?).

<quote>we 'typically' follow a 4+4+5 pattern, but not always.</quote>
So, given a year do you know its quarterly patterns in advance? … or do you have to identify them as well? … and, if yes, which is the algorithm?

<quote>Do you have any suggestions on how I can generate these dates intelligently without having to hardcode values in a table?</quote>
BTW, pre-cooking the FMEs in a Fiscal Calendar Table would hardly constitute hard-coding (great way to share and get a single version of the truth in fact).

To Tom and Gabe -
> explain to me how you are getting to the dates you got
> explain in enlish the logic

That was my precisely my problem as well :-). I wasn't able to decipher the "logic" (or algorithm) to this fiscal calendar myself. I don't have a "functional" background so to speak. I was wondering with your past experience etc, you'd be able to simply see it and tell me "aaha..this is the logic behind this particular fiscal calendar". All the fiscal calendars that I've seen in the past, nicely ended on Dec31. But that's not the case here.

All I have is the printout of fiscal calendars between years 1996 and 2004. I wasn't able to figure out the logic either. I guess I'll have to go back to people that came up with the calendar and ask them.

There is no claim of elegance or performance whatsoever with this procedure ... just brutish code.

(BTW ... my guess is, every year somebody flicks a coin for that FQ4 4-5-5 if the distribution seems wacky ... that is, there may be no consistently applied algorithm at all).

flip@flop> exec p(2005)
== 1996 ======
4: 27 jan 1996
4: 24 feb 1996
5: 30 mar 1996
4: 27 apr 1996
4: 25 may 1996
5: 29 jun 1996
4: 27 jul 1996
4: 24 aug 1996
5: 28 sep 1996
4: 26 oct 1996
4: 23 nov 1996
5: 28 dec 1996
== 1997 ======
4: 25 jan 1997
4: 22 feb 1997
5: 29 mar 1997
4: 26 apr 1997
4: 24 may 1997
5: 28 jun 1997
4: 26 jul 1997
4: 23 aug 1997
5: 27 sep 1997
4: 25 oct 1997
4: 22 nov 1997
5: 27 dec 1997
== 1998 ======
4: 24 jan 1998
4: 21 feb 1998
5: 28 mar 1998
4: 25 apr 1998
4: 23 may 1998
5: 27 jun 1998
4: 25 jul 1998
4: 22 aug 1998
5: 26 sep 1998
4: 24 oct 1998
5: 28 nov 1998
5: 02 jan 1999
== 1999 ======
4: 30 jan 1999
4: 27 feb 1999
5: 03 apr 1999
4: 01 may 1999
4: 29 may 1999
5: 03 jul 1999
4: 31 jul 1999
4: 28 aug 1999
5: 02 oct 1999
4: 30 oct 1999
4: 27 nov 1999
5: 01 jan 2000
== 2000 ======
4: 29 jan 2000
4: 26 feb 2000
5: 01 apr 2000
4: 29 apr 2000
4: 27 may 2000
5: 01 jul 2000
4: 29 jul 2000
4: 26 aug 2000
5: 30 sep 2000
4: 28 oct 2000
4: 25 nov 2000
5: 30 dec 2000
== 2001 ======
4: 27 jan 2001
4: 24 feb 2001
5: 31 mar 2001
4: 28 apr 2001
4: 26 may 2001
5: 30 jun 2001
4: 28 jul 2001
4: 25 aug 2001
5: 29 sep 2001
4: 27 oct 2001
4: 24 nov 2001
5: 29 dec 2001
== 2002 ======
4: 26 jan 2002
4: 23 feb 2002
5: 30 mar 2002
4: 27 apr 2002
4: 25 may 2002
5: 29 jun 2002
4: 27 jul 2002
4: 24 aug 2002
5: 28 sep 2002
4: 26 oct 2002
4: 23 nov 2002
5: 28 dec 2002
== 2003 ======
4: 25 jan 2003
4: 22 feb 2003
5: 29 mar 2003
4: 26 apr 2003
4: 24 may 2003
5: 28 jun 2003
4: 26 jul 2003
4: 23 aug 2003
5: 27 sep 2003
4: 25 oct 2003
4: 22 nov 2003
5: 27 dec 2003
== 2004 ======
4: 24 jan 2004
4: 21 feb 2004
5: 27 mar 2004
4: 24 apr 2004
4: 22 may 2004
5: 26 jun 2004
4: 24 jul 2004
4: 21 aug 2004
5: 25 sep 2004
4: 23 oct 2004
5: 27 nov 2004
5: 01 jan 2005
== 2005 ======
4: 29 jan 2005
4: 26 feb 2005
5: 02 apr 2005
4: 30 apr 2005
4: 28 may 2005
5: 02 jul 2005
4: 30 jul 2005
4: 27 aug 2005
5: 01 oct 2005
4: 29 oct 2005
4: 26 nov 2005
5: 31 dec 2005

create or replace procedure p (an_year number default 1996) as
seed date := to_date('30 dec 1995','dd mon yyyy');

d00 date;

d01 date; d02 date; d03 date; d04 date; d05 date; d06 date;
d07 date; d08 date; d09 date; d10 date; d11 date; d12 date;

d13 date; d31 date;
n pls_integer;
restate boolean;
r pls_integer;

procedure pl(v varchar2) is begin dbms_output.put_line(v); end;
begin
if an_year < 1996 then
raise_application_error(-20000,'year has to be greater than 1995 (because seed is from 1995)');
end if;

for y in 1996..an_year
loop
if y = 1996 then
d00 := seed;
else
d00 := d12;
end if;

d01:=d00+28; d02:=d01+28; d03:=d02+35;
d04:=d03+28; d05:=d04+28; d06:=d05+35;
d07:=d06+28; d08:=d07+28; d09:=d08+35;
d10:=d09+28; d11:=d10+28; d12:=d11+35;

d13:=d12+28;
d31:=last_day(d13);
n := to_number(to_char(d31,'J')) - to_number(to_char(d13,'J'));

--
-- This condition for restating seems to do the trick:
--
if n > 7 then
restate := true;
else
restate := false;
end if;

--
-- This condition works fine till that 01 Jan 2005 as FM12 for FY2004.
-- Interesting enough ... this condition generates a much smoother FM distribution.
-- That is, a Fiscal Month End always falls within the corresponding Calendar Month
-- ... un-comment the block bellow and comment out the one above to see it!
--
/*
r := 0;
for i in 1..n
loop
if to_char(d13+i,'fmDay') = 'Saturday' then
r := r + 1;
end if;
end loop;
if r > 1 then
restate := true;
else
restate := false;
end if;
*/

if restate then
d11:=d10+35; d12:=d11+35;
end if;


pl('== '||to_char(y)||' ======');
pl('4: '||to_char(d01,'dd mon yyyy'));
pl('4: '||to_char(d02,'dd mon yyyy'));
pl('5: '||to_char(d03,'dd mon yyyy'));
pl('4: '||to_char(d04,'dd mon yyyy'));
pl('4: '||to_char(d05,'dd mon yyyy'));
pl('5: '||to_char(d06,'dd mon yyyy'));
pl('4: '||to_char(d07,'dd mon yyyy'));
pl('4: '||to_char(d08,'dd mon yyyy'));
pl('5: '||to_char(d09,'dd mon yyyy'));
pl('4: '||to_char(d10,'dd mon yyyy'));
if restate then
pl('5: '||to_char(d11,'dd mon yyyy'));
else
pl('4: '||to_char(d11,'dd mon yyyy'));
end if;
pl('5: '||to_char(d12,'dd mon yyyy'));
end loop;
end;
/
show errors

Gabe - Thanks for possibly decrypting the logic!

> This condition for restating seems to do the trick:
>if n > 7 then
> restate := true;
> else
> restate := false;
> end if;

I suspect that could very well be it. I'm awaiting confirmation from the folks that came up with the calendar.

I didn't understand your comment about a "smoother FM distribution" though. I looked at the different FME results with and without the above condition..but I don't understand what makes it "smoother"?

Hey "Reader",

By making sure that there are never more than one Saturday left out of the January FM we seem to get FY distributions where every FM ends within the related Calendar Month ... hence, no Calendar Month is ever skipped ... (Note: these are just empiric observations based on 1996-2005 ... not mathematically proven)

Look at 2000 with the "no more than 7 days left in Jan" condition:
== 2000 ======
4: 29 jan 2000
4: 26 feb 2000
5: 01 apr 2000 <== FM for Mar is in Apr
4: 29 apr 2000
4: 27 may 2000
5: 01 jul 2000 <== FM for Jun is in Jul
4: 29 jul 2000
4: 26 aug 2000
5: 30 sep 2000
4: 28 oct 2000
4: 25 nov 2000
5: 30 dec 2000

And here is 2000 with the "no more than 1 Saturday left in Jan" condition:
== 2000 ======
4: 22 jan 2000
4: 19 feb 2000
5: 25 mar 2000
4: 22 apr 2000
4: 20 may 2000
5: 24 jun 2000
4: 22 jul 2000
4: 19 aug 2000
5: 23 sep 2000
4: 21 oct 2000
4: 18 nov 2000
5: 23 dec 2000

This is what I meant by "smoother" distribution.

I'm just curious if I _got_ the FY2005!!!!

Anyway, I think I _abused_ Tom's site enough on this subject ... sorry about the long posting Tom.

This is sort of related to the discussion on this page, so here goes.

How can I generate 4 month-end dates for 4 specified months (say Feb, Jun, Aug, Nov) which are all in the past? So, if I run the query today, I should get Jun 2004, Aug 2004, Nov 2004 and Feb 2005. If I run the query in July 2005, I should get Aug 2004, Nov 2004, Feb 2005 and Jun 2005 and so on.

Thanks

ops$tkyte@ORA9IR2> select dt
  2    from (
  3  select add_months(trunc(dt,'mm'),-l) dt
  4    from (
  5  select add_months(sysdate,0) dt, level-1 l
  6    from dual
  7  connect by level <= 12
  8         )
  9         )
 10   where to_char(dt,'MM') in ( 6, 8, 11, 2 )
 11   order by dt
 12  /
 
DT
---------
01-JUN-04
01-AUG-04
01-NOV-04
01-FEB-05
 
ops$tkyte@ORA9IR2> select dt
  2    from (
  3  select add_months(trunc(dt,'mm'),-l) dt
  4    from (
  5  select add_months(sysdate,2) dt, level-1 l
  6    from dual
  7  connect by level <= 12
  8         )
  9         )
 10   where to_char(dt,'MM') in ( 6, 8, 11, 2 )
 11   order by dt
 12  /
 
DT
---------
01-AUG-04
01-NOV-04
01-FEB-05
01-JUN-05
 
ops$tkyte@ORA9IR2>
 

Never mind, I figured it out. Thanks

  1  select d from (
  2  select last_day(add_months(sysdate,0-rownum)) d from all_objects where rownum<=12
  3* ) where to_char(d,'Mon') in ('Apr','Aug','Dec')
SQL> /

D
---------
30-APR-05
31-DEC-04
31-AUG-04

 

Tom, I have read your reply for 1st question posted in this thread. However, your query gets the correct quarter and did not have logic for corresponding year.
eg:- if july 01 2004 is 1st qtr using your query we get qtr as 1. But how to manipulate year?

Thanks,

select dt "actual date",
to_char(dt,'Q')+1 "Actual Qtr",
to_char(dt,'YYYY') "Actual Year",
mod(to_char(dt,'Q')+1,4)+1 "fiscal Qtr",
case when mod(to_char(dt,'Q')+1,4)+1 >= 3 and mod(to_char(dt,'Q')+1,4)+1<=4 then to_number(to_char(dt,'YYYY'))-1
else to_number(to_char(dt,'YYYY')) end as "Fiscal year"
from (
select add_months(trunc(sysdate,'y'),rownum-1) dt from user_objects where rownum<=12
)
actual date Actual Qtr Actual fiscal Fiscal year
Year Qtr
01/01/2005 2 2005 3 2004
02/01/2005 2 2005 3 2004
03/01/2005 2 2005 3 2004
04/01/2005 3 2005 4 2004
05/01/2005 3 2005 4 2004
06/01/2005 3 2005 4 2004
07/01/2005 4 2005 1 2005
08/01/2005 4 2005 1 2005
09/01/2005 4 2005 1 2005
10/01/2005 5 2005 2 2005
11/01/2005 5 2005 2 2005
12/01/2005 5 2005 2 2005

Am i in right direction?

Here is my fiscal year for 2004
July 01 2004 to jun 30 2005

if that is really "2004" (looks more like a 2005 FY)

to_char( add_months( DT_FIELD, -6 ) , 'YYYY' )


ops$tkyte@ORA9IR2> select dt, to_char(add_months(dt,-6),'yyyy') from t;

DT        TO_C
--------- ----
30-JUN-04 2003
01-JUL-04 2004
02-JUL-04 2004
29-JUN-05 2004
30-JUN-05 2004
01-JUL-05 2005

6 rows selected.


if it should be 2005, fix is "obvious":

ops$tkyte@ORA9IR2> select dt, to_char(add_months(dt,+6),'yyyy') from t;

DT        TO_C
--------- ----
30-JUN-04 2004
01-JUL-04 2005
02-JUL-04 2005
29-JUN-05 2005
30-JUN-05 2005
01-JUL-05 2006

6 rows selected.


 

Thanks for your solution and yes, that is 2004 FY for our State Reports.For 2005 , it goes till Jun 2006 from jul 2005.

Tom,

My fiscal year starts on oct, 1 of the year and end
on sept, 30 of the following year. e.g
10/1/1988 9/30/1989. It is possible to have something
like this?


DESIRED OUTCOME

FISCAL_START FISCAL_END TOTAL_AMOUNT
10/1/1988 9/30/1989 11,448.00
10/1/1989 9/30/1990 15,853.71
10/1/1990 9/30/1991 19,096.00
10/1/1991 9/30/1992 23,762.72
10/1/1992 9/30/1993 37,481.13
10/1/1993 9/30/1994 38,374.44


CREATE TABLE TEST
(
EVENT_NUMBER VARCHAR2(25 BYTE) NOT NULL,
CLAIM_NUMBER VARCHAR2(25 BYTE) NOT NULL,
FISCAL_YEAR VARCHAR2(4000 BYTE),
CHECK_AMOUNT NUMBER
)


Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV2001-07268', '2006-00018', '2001', 150);
Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV2001-07268', '2006-00018', '2001', 1131.42857142857);
Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV2001-07268', '2006-00018', '2001', 1131.43);
Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV2001-07268', '2006-00018', '2001', 9999);
Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV2006-00100', '2006-00035', '2006', 600);
Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV2006-00100', '2006-00035', '2006', 1145);
Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV1987-02937', '1987-02937', '1987', 6230);
Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV1987-02937', '1987-02937', '1987', 58742);
Insert into test
(EVENT_NUMBER, CLAIM_NUMBER, FISCAL_YEAR, CHECK_AMOUNT)
Values
('EV1987-02937', '1987-02937', '1987', 200);
COMMIT;

Tom,

Right now I am getting this with your help.


EVENT_NUMBER CLAIM_NUMBER FISCAL_YR_BEGIN FISCAL_YR_END SUM(CHECK_AMOUNT)

EV1980-00062 1980-00021 10/1/1980 9/30/1981 183821.09
EV1980-00062 1980-00021 10/1/1980 9/30/1981 1352840.91

However, I would like to have it like this ..
all the claim for that fiscal year and their total payment.

Fiscal Start Fiscal End Total Payments
10/1/1986 9/30/1987 0.00
10/1/1987 9/30/1988 0.00
10/1/1988 9/30/1989 11,448.00
10/1/1989 9/30/1990 15,853.71
10/1/1990 9/30/1991 19,096.00
10/1/1991 9/30/1992 23,762.72
10/1/1992 9/30/1993 37,481.13
10/1/1993 9/30/1994 38,374.44
10/1/1994 9/30/1995 39,540.46
10/1/1995 9/30/1996 40,659.17

Thanks! ;)

Hi
Our fiscal year start date is 1st july and ends on 30-jun.
I want to get the last month of the quarter on the basis of any input date.For e.g if i enter 05-oct-2006 then it should return 31-dec-2006.
Thanks

just math....


ops$tkyte%ORA10GR2> with dates
  2  as
  3  (select add_months(to_date('01-jan-2006'),level-1) dt
  4     from dual
  5  connect by level <= 12)
  6  select dt,
  7         mod(3-mod( to_char(dt,'mm'),3),3) addme,
  8         last_day( add_months( dt, mod(3-mod( to_char(dt,'mm'),3),3) ) )
  9    from dates
 10  /

DT             ADDME LAST_DAY(
--------- ---------- ---------
01-JAN-06          2 31-MAR-06
01-FEB-06          1 31-MAR-06
01-MAR-06          0 31-MAR-06
01-APR-06          2 30-JUN-06
01-MAY-06          1 30-JUN-06
01-JUN-06          0 30-JUN-06
01-JUL-06          2 30-SEP-06
01-AUG-06          1 30-SEP-06
01-SEP-06          0 30-SEP-06
01-OCT-06          2 31-DEC-06
01-NOV-06          1 31-DEC-06
01-DEC-06          0 31-DEC-06

12 rows selected.


Just take your date column DT and:

last_day( add_months( dt, mod(3-mod( to_char(dt,'mm'),3),3) ) ) 

I need to generate a report for last quarter everytime it is run.

For example,
if the report is run today (02/26/2008), I should get the data for last quarter of year 2007
if the report is run on (04/20/2008), I should get the data for 1st quarter of 2008

Please let me know how to ge the dates in this scenario

ops$tkyte%ORA10GR2> select add_months(trunc(sysdate,'q'),-3), trunc(sysdate,'q') from dual;

ADD_MONTH TRUNC(SYS
--------- ---------
01-OCT-07 01-JAN-08

Create table and insert statements for the for the above question:

create table test_quarter
(id number
,dt date
,value varchar2(20)
);

insert into test_quarter
values
(1,to_date('10/02/2007','mm/dd/yyyy'), 'A');

insert into test_quarter
values
(2,to_date('11/02/2007','mm/dd/yyyy'), 'B');

insert into test_quarter
values
(3,to_date('12/02/2007','mm/dd/yyyy'), 'C');

insert into test_quarter
values
(4,to_date('01/02/2008','mm/dd/yyyy'), 'D');

insert into test_quarter
values
(5,to_date('02/02/2008','mm/dd/yyyy'), 'E');

commit;

I need to get the id's in the test_quarter table as per the below requirement:

if the report is run today (02/26/2008), I should get the data for last quarter of year 2007
- ids: 1,2,3 in the result set

if the report is run on (04/20/2008), I should get the data for 1st quarter of 2008
- id's: 4,5 in the result set


Please advice how to get the data.

Tom,
Thanks for your answer.

If I need to get the date range for last quarter of 2007. Can I use this query:

select add_months(trunc(sysdate,'q'),-3)
,last_day(add_months(add_months(trunc(sysdate,'q'),-3),2))
,trunc(sysdate,'q')
from dual;

BEGINNING_DT ENDING_DT TODAY
----------- ----------- -----------
01-OCT-2007 31-DEC-2007 01-JAN-2008


If you have a better option, please let me know.

Please ignore the above question.

Now you can use EXTRACT (QUARTER FROM my_date) instead of TO_NUMBER(TO_CHAR(my_date,'Q')).

If I had time, I'd love to compare performance of these, but my bet is on EXTRACT being slightly faster.

Oops - QUARTER is not (was it ever?) an option for EXTRACT.