Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with formula 1

Status
Not open for further replies.

ekta22

IS-IT--Management
May 3, 2004
359
US
I have to work on this complicated formula and need some help with it.
I have these two columns that look like below (Right now the columns below are not in any table)

Code:
[b]Column 1			Coulmn 2[/b]
[b]DD[/b] Daily			001DD000DD
[b]SW[/b] Semi-Weekly	  003DD001DD
[b]M[/b] Monthly			001MM007DD
[b]SA[/b] Semi-Annual	  006MM030DD
[b]A[/b] Annual			001YY060DD
and so on...

I have these 3 fields in my table - Interval_length, Earliest_Date, Scheduled_Date.
I first need to check the data in my table for interval_length and if it matches any value in column 1 then I move to column 2.
So for example if interval_length in my record is M and it matches the 3rd value in column 1, I look at the corresponding value 001MM007DD
in column 2 and use that to do further calculations.
Based on 001MM007DD I need to calculate the next Scheduled_Date, Earliest_Date. First 5 letters, 001MM tells me the next scheduled date.
So if my current scheduled_date is 06/08/05 the next scheduled_date will occur after 1 month i.e. 07/08/05. After calculating this part
the remaining date fields Earliest_Date and Latest_Date are calculated. To calculate these fields we look at the remaining text in
001MM007DD i.e 007DD. So the new earliest_date will be -7 days from the new scheduled date.

So it will look like
Code:
Interval_length	Scheduled_Date	Earliest_Date	
M		         06/08/05	      05/28/05	
M		         07/08/05	      07/01/05(after calculation)	

Another example for interval SW and 003DD001DD would be

Interval_length	Scheduled_Date	Earliest_Date	
SW		        06/08/05	     05/28/05	
SW		        06/11/05	     06/10/05

I would appreciate any help.
 
There could be another way, this depend on the fact that you could add days all the time in which case CR will take care of every issue you might have

eg:
D
date({pPMPERDET.SCHEDULED_DATE})+ 1
m
date({pPMPERDET.SCHEDULED_DATE}) + 30
SA
date({pPMPERDET.SCHEDULED_DATE}) + 182

this is not entirely correct because months could be 30, 31 and one of 28

and six months depending form where you start could be less or more than 182 day, but is very close and would simplify the whole process.

Mo
 
I am totally confused now. You are right there are months with 30, 31 and 28 days and also leap year. What do you think is the best way to handle all this? I don't want it to be close but absolutely correct.
 
You can use a combination of both,

keep the formula that I gave for the single and the six months
Code:
else if IntLenght = "M" then
(
if month(MyDate+ 1) > 12 then
Date (year(MyDate )+ 1,month(MyDate)+ 1 -12 , day(MyDate))
else
Date (year(MyDate),month(MyDate )+ 1 , day(MyDate))
)
else if IntLenght = "SA" then
(
if month(MyDate)+ 6 > 12 then
Date (year(MyDate )+ 1, month(MyDate ) + 6 - 12 , day(MyDate))
else
Date (year(MyDate),month(MyDate )+ 6 , day(MyDate))
)
else if IntLenght = "5Y" then
Date (year(MyDate)+ 5,month(MyDate) , day(MyDate))

and then you use the other option for the days

Code:
else if IntLenght = "D" then
  date({pPMPERDET.SCHEDULED_DATE})+ 1
else if IntLenght = "SW" then
  date({pPMPERDET.SCHEDULED_DATE})+ 1
else if IntLenght = "28D" then
  date({pPMPERDET.SCHEDULED_DATE})+ 28

this way you don't have toworry about overlapping month when adding days

Mo
 
Thanks so much Mo. It works great..:). I used DateAdd instead. Thanks for your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top