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 John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Start Date - End Date

Status
Not open for further replies.

TEM3

Technical User
Dec 6, 2004
324
US
Using Crystal Reports 8.5 and Oracle 9i tables:

In our system a CASE can have several section assignments. They can be assignments to different sections and/or multiple assignments to the same section. Each assignment starts with an assignment date and eventually gathers a date completed. I need to measure the CASE Assignment turn-around-time for a particular section, no matter how many assignments were created for that case in the same section.

So, if a CASE has multiple assignments to the same section, I need to find and store the first assignment date as the Start Date and then need to find the last (latest) completed assignment date for that section as the End Date and subtract the two (these would be in different records in the case of multiple sections assignments).

So, the way my tables are set up, for a particular CASE (Case Key), I would need to parse the REPORT (completed assignment) table for any assignments for a particular section with that Case Key, find and store the earliest Assignment Date found, find and store the latest Completed Date found and do a date subtract.

How do I get Crystal to store the two date extremes while parsing all the appropriate records in my REPORT table?
 
How about this plan of attack:

I group on Case and then Section. In the group header of the Section group I initilize my two date variables by setting StartDate to {Date Assigned} and EndDate to {Date Completed}. I create a formula for each which I place (both formulas) in the details section. Formulas would be:

If StartDate < {Date Assigned} then StartDate = {Date Assigned}

and

If EndDate > {Date Completed} then EndDate = {Date Completed}.

By the time we get to the Section group footer we have collected the lowest Date Assigned in StartDate and the higest Date Completed in EndDate. Then doing a EndDate - StartDate I get the turn-around-time for that particular Case-Section assignments.

Sound like a plan??


 
I think you could use:

datediff("d",minimum({DateAssigned},{table.section}),maximum({DateCompleted},{table.section}))

...assuming that you do insert a group on {table.case} and then on {table.section}

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top