6 Replies Latest reply: Jul 22, 2013 4:12 PM by Andrea Reyes

Calculation during load from resident table data in memory

Here is the situation.... I am about to load data from a resident table, with code like....

Table:

[Arrival DateTime],

[Screen DateTime],

[Depart DateTime]

RESIDENT Temp_ET;

I want to do a date difference in minutes between [Arrival DateTime] and [Screen DateTime] as well during the load...BUT if one of them is null then I want to return null obviously. Also, if for some reason in the data, the [Screen DateTime] is earlier than the [Arrival DateTime], then I also want to return null instead of a negative value. How can I add this calculation into the load?

• Re: Calculation during load from resident table data in memory

Brian, this should work (if I got the rules right):

Table:
if([Arrival DateTime] < [Screen DateTime]
and len(trim([Arrival DateTime]) >0
and len(trim([Screen DateTime]) >0,
[Screen DateTime] - [Arrival DateTime]) as Difference,
[Arrival DateTime],
[Screen DateTime],
[Depart DateTime]
RESIDENT Temp_ET;

Regards,

Michael

• Re: Calculation during load from resident table data in memory

Will this give minutes?

• Re: Calculation during load from resident table data in memory

It will give days.  But you can use interval function for a better formatting:

if([Arrival DateTime] < [Screen DateTime]

and len(trim([Arrival DateTime]) >0

and len(trim([Screen DateTime]) >0,

interval([Screen DateTime] - [Arrival DateTime], 'DD hh:mm') as Difference,

So you get it in days, hours, and minutes

• Re: Calculation during load from resident table data in memory

This is very helpful Michael, but I actually want the actual data to just be the number of minutes as an integer...

• Re: Calculation during load from resident table data in memory

In this case, you can multiply the difference the days by number of minutes in a day, 24*60, no need for interval:

([Screen DateTime] - [Arrival DateTime]) * 24 * 60

I think that interval can be used too, but use only minutes:

interval([Screen DateTime] - [Arrival DateTime], 'mm')

• Re: Calculation during load from resident table data in memory

replace the format ' 'DD hh:mm'' by 'mm':

interval([Screen DateTime] - [Arrival DateTime], 'mm')