Capture true time interval (it's a brain teaser)

[#1]

I am trying to capture a turn time order to completion. I have to subtract out all the time an order was on dealy from the order to completion interval to get an accurate number.

The issue is that there can be multiple delays on and order and they can stop and start at any time. In some cases a multiple dealys may start before a previous delay is completed. I can capture the turn time of the delay entry to completion but I want to be able to subtract out the over lapping time if that makes sense.

The solution may not be QlikView specific. There may be a way to do this in SQL? I am including an example of one order with multiple delays to illustrate the problem.

Thanks

[#2]

Hi David,

i would suggest the following load script to calculate the "Real" delay time each delay has. For this to work you must ensure that the records are sorted by ORDER_CDE and by start time:

Directory;

LOAD

ORDER_CDE,

ORDER_DELAY.ENTERED_DTE

,

ORDER_DELAY.CMPLTD_DTE

,

[Delay Turn Time]

,

[Order to completion Turn Time]

,

//Check with previous record

If(previous(ORDER_CDE)=ORDER_CDE,

IF

(ORDER_DELAY.ENTERED_DTE < previous(ORDER_DELAY.CMPLTD_DTE),

IF

(ORDER_DELAY.CMPLTD_DTE < previous(ORDER_DELAY.CMPLTD_DTE),0,

ORDER_DELAY.CMPLTD_DTE

-previous(ORDER_DELAY.CMPLTD_DTE)),

ORDER_DELAY.CMPLTD_DTE

-ORDER_DELAY.ENTERED_DTE),

ORDER_DELAY.CMPLTD_DTE

-ORDER_DELAY.ENTERED_DTE) as RealDelayTime

FROM

example.xls

(

biff, embedded labels, table is Sheet1$);

Regards,

Joao

[#3]

Thanks, let me play around with this and see what I can come up with