gems
June 9th, 2005, 11:54 AM
I have to work on this complicated formula and need some help with it.
I have these 3 fields in my table - Interval_length, Scheduled_Date.
I need to calculate the new scheduled_date and earilest_date based on the above 3 fields which are already in my table for all the records that meet a certain criteria and then display the count of records found.
Sample date in my table ri now is
Interval Length - M
Scheduled_Date - 06/08/05
I have these two columns that look like below (Right now the data below is not in any table)
Column 1 Coulmn 2
DD Daily 001DD000DD
SW Semi-Weekly 003DD001DD
M Monthly 001MM007DD
SA Semi-Annual 006MM030DD
A Annual 001YY060DD
and so on...
To calcualte the new scheduled_date, I first need to check the records in my table and if the data for the field interval_length matches any value in column 1 then I look at column 2 to calculate the new scheduled_date.
So for example if interval_length in my record is M and it matches the 3rd value in column 1 (that is also M), I look at the corresponding value 001MM007DD
in column 2.
Based on 001MM007DD I need to calculate the new Scheduled_Date and Earliest_Date. First 5 letters, 001MM tells me the new scheduled date.
My current scheduled_date is 06/08/05. The new scheduled_date will occur after 1 month i.e. 07/08/05. After calculating the new scheduled_date, I need to calculate the new Earliest_Date, which is calculated from the new scheduled_date. To calculate this field 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
Interval_length Scheduled_Date Earliest_Date
M 06/08/05 05/28/05 (data in table)
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 (data in table)
SW 06/11/05 06/10/05 (after calculation)
After calculating the new earliest_date for all the records in my table, I need to compare it with another field in my table, end_date and if new Earliest_date <= end_date then display count of records found.
I hope it's not too confusing. Another thing, should I create a new table in my SQL database with data for those 2 columns or can it be saved in the formula itself? I would appreciate any help.
I have these 3 fields in my table - Interval_length, Scheduled_Date.
I need to calculate the new scheduled_date and earilest_date based on the above 3 fields which are already in my table for all the records that meet a certain criteria and then display the count of records found.
Sample date in my table ri now is
Interval Length - M
Scheduled_Date - 06/08/05
I have these two columns that look like below (Right now the data below is not in any table)
Column 1 Coulmn 2
DD Daily 001DD000DD
SW Semi-Weekly 003DD001DD
M Monthly 001MM007DD
SA Semi-Annual 006MM030DD
A Annual 001YY060DD
and so on...
To calcualte the new scheduled_date, I first need to check the records in my table and if the data for the field interval_length matches any value in column 1 then I look at column 2 to calculate the new scheduled_date.
So for example if interval_length in my record is M and it matches the 3rd value in column 1 (that is also M), I look at the corresponding value 001MM007DD
in column 2.
Based on 001MM007DD I need to calculate the new Scheduled_Date and Earliest_Date. First 5 letters, 001MM tells me the new scheduled date.
My current scheduled_date is 06/08/05. The new scheduled_date will occur after 1 month i.e. 07/08/05. After calculating the new scheduled_date, I need to calculate the new Earliest_Date, which is calculated from the new scheduled_date. To calculate this field 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
Interval_length Scheduled_Date Earliest_Date
M 06/08/05 05/28/05 (data in table)
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 (data in table)
SW 06/11/05 06/10/05 (after calculation)
After calculating the new earliest_date for all the records in my table, I need to compare it with another field in my table, end_date and if new Earliest_date <= end_date then display count of records found.
I hope it's not too confusing. Another thing, should I create a new table in my SQL database with data for those 2 columns or can it be saved in the formula itself? I would appreciate any help.