Good afternoon

Trying to work out a formula

I basically need to create within Crystal - the amount of days between the previous job finished and the next call logged - if this is more than 14 days this is should say "FTF" ( first time fixed ) if less then put "NULL"
This would need to be based on the same Serial No only - as this would have multiple Cust Order Numbers

Below is an example of some data based on the above


Customer order number     Serial number     Call Logged Count     Actual Finish Date     
0005267219     C517206     23/10/2013     29/10/2013       
0005494652     C517206     27/12/2013     27/12/2013 59
0005650378     C517206     15/02/2014     17/02/2014 50
0005724493     C517206     09/03/2014     09/03/2014 20

The count at the end is the amount of days between the logged and finished date - which is what I need it to look like in crystal

I was thinking of using running total maybe - but any ideas would be good

Thanks
Andy
59

Edited by ajp42 - 31 Oct 2014 at 6:46am

thanks for this - doesn't seem to work though
Below is the example I have put in

if next({SSJOBH.Customer order number}) <> {SSJOBH.Customer order number}then '' else

if next({SSJOBH.Customer order number}) = {SSJOBH.Customer order number} and datediff('d',{@Actual Finish Date},next({@Call Logged}) > 14 'FTF' else 'NULL'

The formula doesn't work - brackets I think seem to be the issue but I cannot work out what needs to change

Edited by ajp42 - 03 Nov 2014 at 1:42am

Thanks for that - this has worked but now has created another problem

The below is the formula I have in query

if next({SSJOBH.Serial number}) <> {SSJOBH.Serial number}then '' else
if next({SSJOBH.Serial number}) = {SSJOBH.Serial number} and datediff('d',{@Actual Finish Date},next({@Call Logged})) > 14 then 'FTF' else ''

The above is based on machine items - but if there is no record after the last one per machine item I need it also to say FTF - as this would mean we have had no more visits - as long as I haven't included it in the above query within 14 days
Also - lines that only have one logged call - has not been counted - so would need to be a FTF
I have sorted the data by Serial Number as there are multiple serial numbers - so the last one would be a FTF if no return

Edited by ajp42 - 03 Nov 2014 at 4:14am

Thanks for your help - I have worked this out myself

I may come back to you again