Finding Timedifference difference on multiple datetime

[#1]

Hello

I have a qvd which is split everymonth due to huge data approximately 2000000 rows and loading this directly is Optimized, however I have to subtract Operations in Datetime format, I have close to 19 operations

my table looks like the one I have attached to this post

Need to do:

1. Subtract each Datetimefield to find the interval.

2. if the previous operation datetime is not available,it has to take previous to that field.

Unsuccessful.

I tried to do a cross table and subtract the previous date to find the interval, but the table becomes very huge.

2. Trying to create a QVD, but I need to do a incremental load, after crossing the ID multiplies so the insertupdate mode doesnt work

3.Loading the cross table in application instead of QVD, it takes lot of time to load the entire 12 months data

HELP:

What is the best approach for this time of scenario, any help is much appreciated

Regards

AJ

[sunny_talwar]

Would you be able to create a few lines worth of sample with your expected output to help you better? Add the data in its rawest form and tell us how you would like the final result to look like?

[#3]

Hi Sunny,

attached a sample in excel, it has to subtract the previous datetime (ie is the operation datetime) if the previous operation is missing then it has to take the previous to previous operation as shown in the excel,

Ideally I need to group the operations to set of workshop and by selecting the month I will know what is the average time taken for particular workshop for a month

Regards

AJ

[#4]

Sample excel file attachd