Date Calculation question

[JosephLaz]

Hi,

I am in the process of creating a report that will report the number of permits that have met date event milestones. Each permit has a number of milestones to meet (example below) and the dates are calculated using the dates in the EventDesc column. The question I have is this: Using the example table and data below, how can I calculate the date difference (in days) between say EventDateID (7) and EventDateID (3) :

PermitID PermitNum EventDate EventDesc EventDateID
------------------------------------------------------------
23 PSD-1206 5/11/06 App Received 1
23 PSD-1206 5/25/06 Determination 2
23 PSD-1206 7/25/06 Complete App Rec 3
23 PSD-1206 8/25/06 Process 5
23 PSD-1206 9/25/06 Disclosure 6
23 PSD-1206 11/22/06 Final Decision 7


I tried using the PreviousValue function, but that only allows calculation between the record previous to it like EventDateID (7) and (6) above.
Thanks

 

[synapsevampire]

You need to rethink how you share requirements.

I can show you how to do it, but it's hard coding for those specific IDs, which likely isn't your requirement, you're just sharing one part of it.

Are you saying that all you ever need is to tell the difference between ID's 7 and 3?

Successful posts share technical info:

Crystal version
Database/connectivity
Example data
Expected output

So show what is to be output and where.

-k

 

[dgillz]

Are you grouping by PermitNum?

Is there ever a possibility of having more than one 3 or 7 record per PermitNum? If so which one do you want to use for the calculation?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[JosephLaz]

Both of you who reply brought up some good questions and here are the answers:

1. Are you saying that all you ever need is to tell the difference between ID's 7 and 3? No, there are other date difference calculations between other ID's as well.

2. Is there ever a possibility of having more than one 3 or 7 record per PermitNum? No, each permit will have its own set of events, and each event will occur only once for that permit.

 

[JosephLaz]

I'm sorry I forgot to answer this other question:

3. Are you grouping by PermitNum? No, there is no grouping. This is strictly data brought in and formulas used to provide calculations and statistics on that data.

 

[lbass]

You could create a separate formula for each eventdateID like this:

//{@3}:
if {table.eventdateID} = 3 then {table.eventdate}

//{@7}:
if {table.eventdateID} = 7 then {table.eventdate}

If you are working with more than one permit ID, you should insert a group on permit ID, and use a a formula like this in the group header or footer for the datediff:

datediff("d",maximum({@3},{table.permitID}),maximum({@7},{table.permitID}))

If there is only one permit ID per report, then remove the group condition from each maximum in the formula.

-LB