3 Replies Latest reply: Jul 11, 2012 8:55 AM by Stefan Wühl

# calculate a datetime difference

Hi

I do have the following Table:

DateTimeFieldoldValuenewValue
01.07.2012 - 08.04created
01.07.2012 - 08.15statusopeninProgress
02.07.2012 - 10.15OwnerUser1User2
03.07.2012 - 14.50statusinProgressclosed

now I need to calculate the total time needed for the Document. I need no know the difference from the create till the status = closed. There is only one DateTime in that history table and also other status which I do not need to calculate. Just the difference from beginning to the end.

How can I solve that? Can anybody help me to solve that?

Thanks a lot!

• ###### Re: calculate a datetime difference

Maybe something like this?

DocumentID,

interval(only(if(Field='status' and newValue='closed',DateTime)) - only(if(Field='created', DateTime)),'hh:mm') as DateTimeDiff

resident YOURTABLE group by DocumentID;

• ###### Re: calculate a datetime difference

Thank you swuehl

It works not like this. I get no result on each line and it creates a new Table "Tabelname -1".

I do not understand it really...

The original Table is CaseHistory. There I do have an ID (%CaseID) to a second Table, calling Case.

I do get the History Values per Case inside that History Table.

btw I did load the Tables from a qvd File and use qualify and unqualify % to not generate syntetic keys.

Is it easier to calculate the date-diff for each step? But how can QV now the right sort order, how to tell QV which is the first entry, the second and so on?

Thanks a lot for helping me.

My code is:

%CaseID,
interval(only(if(CaseHistory.Field='status' and CaseHistory.NewValue='closed',CaseHistory.CreatedDate))
-
only(if(CaseHistory.Field='created', CaseHistory.CreatedDate)),'hh:mm') as DateTimeDiff
resident CaseHistory group by %CaseID

;

• ###### Re: calculate a datetime difference

I still think the approach should work.

You need to take care that your DateTime is interpreted correctly as Timestamp, e.g. by using an appropriate standard format.

You table is called "Tabelname -1", because we haven't set a specific different name, so QV just follow the name pattern of the previous table. We can correct this by explicitely stating a table name:

SET TimestampFormat='DD.MM.YYYY - hh.mm';

QUALIFY *;

UnQualify %CaseID;

CaseHistory:

%CaseID, DateTime,    Field,    oldValue,    newValue

C0815,01.07.2012 - 08.04,    created,        ,

C0815,01.07.2012 - 08.15,    status,    open,    inProgress

C0815,02.07.2012 - 10.15,    Owner,    User1,    User2

C0815,03.07.2012 - 14.50,    status,    inProgress,    closed

];

CaseTimeDiff: