Generation of Financial Year calender-1st July to 30th June

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
satyame171
Participant
Posts: 13
Joined: Tue May 09, 2006 2:19 am

Generation of Financial Year calender-1st July to 30th June

Post by satyame171 »

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
satheesh
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
kcbland
Participant
Posts: 5208
Joined: Wed Jan 15, 2003 8:56 am
Location: Lutz, FL
Contact:

Post by kcbland »

Say it with me now everyone: TIME dimension.
Kenneth Bland

Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Post Reply