Hi all,
I have developed a job for generating the records for the financial year calender. The input is 30th June 2005 and it should start the calender from 1st July 2005.
The attributes of the target table are:
DATE_ID NUMBER
DATE_VAL DATE
MTH_SHRT_NAM VARCHAR2(5 Bytes)
MTH_NAM VARCHAR2(10 Bytes)
MTH_SHRT_NAM_WITH_YR VARCHAR2(20 Bytes)
MTH_LONG_NAM_WITH_YR VARCHAR2(20 Bytes)
MTH_NUM_IN_YR NUMBER
MTH_NUM_WITH_YR VARCHAR2(10 Bytes)
MTH_LST_DAY_IND VARCHAR2(1 Bytes)
YR NUMBER
QUARTER VARCHAR2(10 Bytes)
QUARTER_WITH_YR VARCHAR2(20 Bytes)
QUARTER_LST_DAY_IND VARCHAR2(1 Bytes)
HALF_YR VARCHAR2(10 Bytes)
HALF_YR_WITH_YR VARCHAR2(20 Bytes)
WK_NUM_IN_MTH NUMBER
WK_NUM_IN_YR NUMBER
DAY_NUM_IN_WK NUMBER
DAY_NUM_IN_MTH NUMBER
DAY_NUM_IN_YR NUMBER
So, for example if we do a query on the table for the yr=2005 , then we should get values from 1st July 2005 to 30 th June 2006.
The job is done usding Iconv and Oconv functions, but as of now i am facing problem with 2 attributes, which are
WK_NUM_IN_YR and
DAY_NUM_IN_YR
Could anyone please help me out with the logic for the calculation of the same for Financial year. For example, the first week in the year should be the one in which 1st july 2005 comes and so on...
Right now the logic for DAY_NUM_IN_YR is as follows:
Oconv(LNK_READ_DATE_CTRL.DATECONV_NO,"D-J")
where LNK_READ_DATE_CTRL.DATECONV_NO = Iconv(SvStartDate,"D-YMD[4,2,2]") + SvRunNo,
SvStartDate = '2005-06-30',
SvRunNo=SvRunNo+1 and the initial value for SvRunNo is 0.
The logic for the WK_NUM_IN_YR is as follows:
WeekinYear(LNK_READ_DATE_CTRL.CAL_DT)
where LNK_READ_DATE_CTRL.CAL_DT = Oconv(SvIConvDate,"D-YMD[4,2,2]"),
SvIConvDate=Iconv(SvStartDate,"D-YMD[4,2,2]") + SvRunNo
Generation of Financial Year calender-1st July to 30th June
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 13
- Joined: Tue May 09, 2006 2:19 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Before embarking on week manipulation you need to get your business rules right for week number. How is week #1 defined? Take a look at the WeekTag routine (category Examples\Functions) to see what I mean; their rule is documented in comments at the top of the code. Of course this is for a calendar year - you must document a rule for your financial year. Do you need every week to contain seven days?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.