How do I display all the due dates on a repetitive action

[blamora]

I want to display all the due dates within a perscribed period for a repetetive action, where the frequency of the action is every 30 or 90 days and the forcasted start date is one year in the future with an ending date one year later?

 

[rosemaryl]

Dear Blamora,

I am afraid that you have not provided enough information upon which to formulate an answer.

Can you please provide:

Crysatl Version
DB Platform
Sample Data
Expected output

If you can do that, I am sure that you will be much closer to an answer.

Thanks,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[blamora]

My report takes a base inspection date for a specific item and forcasts upcoming dates between the beginning and ending date parameters. I want to view all the dates within the parameters in the detail section of the report.

 

[rosemaryl]

Dear Blamora,

And do these dates exist in the database? Can you please answer my other questions. I cannot formulate an answer unless I have the data I requested.

Thanks,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[blamora]

From the base date, the next inspection date is calculated in the database. The remaining dates I need are created using a formula adding the date with the inspection interval. As an example:

If the inspection interval is every 30 days my report looks like this

Last_Done Next_Due Forcast_Date
01/20/03 02/19/03 03/17/03

I want to start the forcast one year out and check all the dates the inspection will be due for a one year period

 

[IdoMillet]

Create a VIEW in the database (or SQL query as the report data source, or stored procedure...) that looks like this:
------------------------------------------------------
SELECT X, Y, Z, Inspection_Date FROM Your_Table
UNION ALL
SELECT X, Y, Z, Inspection_Date + 1*{Days_Between} FROM Your_Table
UNION ALL
SELECT X, Y, Z, Inspection_Date + 2*{Days_Between} FROM Your_Table
UNION ALL
SELECT X, Y, Z, Inspection_Date + 3*{Days_Between} FROM Your_Table
...
------------------------------------------------------

All the current (1st part of the UNION query) and future (following parts in the UNION query) inspection dates would show up under the inspection_date column and you can apply a WHERE clause to restrict the date range for which you want results.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

I don't see how:

If the inspection interval is every 30 days my report looks like this

Last_Done Next_Due Forcast_Date
01/20/03 02/19/03 03/17/03

Has anything to do with a forecast date of 1 year from a date?

Anyway, if you want to increment a date by 30 days, use the dateadd function:

dateadd("d",30,{table.date})

So if you want the next 2 dates, increamenting 30 days each, create 2 formulas:

dateadd("d",30,{table.date})

and

dateadd("d",60,{table.date})

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[blamora]

The interval for each task is different. Using a dateadd as you suggest wont work. I have to use the actual_interval. I can get the formula to print the last date of the date range parameter. I want to see all the dates within the parameter. This is the formula I am using to get to the last date.

Dim ExtDate
Dim Interval as Number

Interval = {ACT1.ACTUAL_INTERVAL}
ExtDate = {ACT1.LAST_DATE}

While ExtDate <= {?Ending Date}
Formula = ToText(ExtDate)
ExtDate = ExtDate + Interval
Wend