Date diff between two dates on different records

[lissa1974]

Hi there, using crystal 2008 on SQL.

I currently have a report that shows....

Stage 1%

01/12/2008

Stage 2%

10/12/2008

Stage 3%

21/12/2008

Basically they are three history records related to the same company, so it keeps track of when their issue is moved forward. Every time it moves forward it creates a new history record with the date.

What I need to do is calculate how long it takes in days to get from stage 1% to stage 3% (for instance). Having difficulty in doing this, any ideas?

Thanks

 

[lbass]

Do you have one field that has instances "stage 1%", "stage2%", etc.? Do you need the days difference for the earliest to the most recent date? Or do you need the difference for selected stages?

-LB

 

[lissa1974]

Hi there, yes sorry there is one field that contains the stage 1, stage 2 etc....

So one record would be

Company=Tek-tips PLC (company table)
Stage 1% - 20/12/2008 (history table)
Stage 2% - 24/12/2008 (history table)

so one to many from company to history.

I need to be able to say the days difference between what is in the Stage field....

so "how many days between stage 1% and stage 4%", "how many days between stage 3% and stage 5%" etc....

I then need to flag records that say don't move from 2% to 4% within 10 days, which I don't think will be a problem once the above section is worked out (I hope!)

Thanks

 

[lbass]

First insert a group on the company field, and then create a conditional formula for each stage, e.g.,

//{@stage1dt}:
if {table.stage} = "Stage 1%" then {table.date}

//{@stage3dt}:
if {table.stage} = "Stage 3%" then {table.date}

Then create formulas to compare them like this:

datediff("d",maximum({@stage1dt},{table.co}), maximum({@stage3dt},{table.co}))

You could color the groupname by right clicking->format field->color->background->x+2 and entering, e.g.:

if datediff("d",maximum({@stage1dt},{table.co}), maximum({@stage3dt},{table.co})) > 10 then
cryellow else
crnocolor

-LB

 

[lissa1974]

That's done the trick, thanks once again for your help.