Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is the situation.... I am about to load data from a resident table, with code like....
Table:
LOAD
[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?
Brian, this should work (if I got the rules right):
Table:
LOAD
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
Will this give minutes?
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
This is very helpful Michael, but I actually want the actual data to just be the number of minutes as an integer...
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')
replace the format ' 'DD hh:mm'' by 'mm':
interval([Screen DateTime] - [Arrival DateTime], 'mm')