rolling forward of date

[sonia jacob]

Hi,

I need to roll forward Date1 till it crosses Date2 based on a frequencies (Monthly, Bi yearly, Quarterly etc.)


eg
Date1 = 01/01/1997
Date2 = 09/30/2004
Frequency = Monthly

Forwarded Date = 10/01/2004


Date1 = 01/01/1997
Date2 = 09/30/2004
Frequency = Quarterly

Forwarded Date = 12/01/2004

and so on

I have a routine which would add the appropriate frequency <b>ONCE</b> to Date1 and return the Forwarded Date. When I try using it in a loop (till forwarded date >= Date2) the performance of the job goes down from 300 rows per sec to <1 row per sec.

Testing the routine for a set of values from the source seems to work fine and fast.

1. Have anybody faced a performance issue when a routine that loops is used?

2. Do you think there would be other ways I can implement the rule?

Thanks

Sonia

[dsxdev]

If you are looking at incrementing the date you can you functions available in transformer

You can get days between two dates and add appropriate multiple of ther frequency and get the date back. let us see for quarterly(90days)

Code: Select all

DateFromDaysSince(90-Mod(DaysSinceFromDate(Date1,Date2),90),Date2)

Step one

DaysSinceFromDate(Date1,Date2) would return no of dys between these two dates if you have to increment date1 in multiples then it it same as adding the difference between the (date2-date1) and the next multiple of frequency to date 2

Date1 = 01/01/2003
Date2 = 09/30/2004

(2004-09-30)-(2003-01-01)=637
next multiple would be 90*8=720

so next date would be (09/30/2004)+83 days

[sonia jacob]

then do you think you would be able to get the date part correctly. I had to write logic to decide whether i have to use 90,91,92 days based month and leap year.

[ray.wurlod]

Presumably you have a time dimension table in your DW. You can roll forward until the desired attribute (as retrieved from that table) changes value.

[sonia jacob]

hi Ray,

well this is not a DW kind of environment. We use DS as a bridge between two systems. one system sends me the source file and i send the data to the next system as a flat file. Even though I would like I have no Database to get the "DATE" data from.

thanks and regards
sonia

[mhester]

Even though you are not in a "DW" environment you could still build a time dim or whatever you want to call it and use it as Ray points out. This really would be a simple and fast solution to your problem. You can also custom tailor your table to meet the needs of your specific processing requirements.

Regards,