Hi everyone I got an issue,
How can I subtract the arrival and depart (date_time) in to deviation (hours) and extract it to difference.
I have done the deviation by using this:
case(> when(diff_days(${arrival}, ${depart}) = 0> , (extract_hours(${depart}) - extract_hours(${arrival})) +> > (extract_minutes(${depart}) - extract_minutes(${arrival}))/60 +> > (extract_seconds(${depart}) - extract_seconds(${arrival}))/3600)> , diff_minutes(${arrival},${depart})/60)
and the difference I did the same but it got an issue like this:
Many thanks
2 Likes
Deviation (hour):
case(> when(diff_days(${date 1}, ${date 2}) = 0> , abs((extract_hours(${time 1}) - extract_hours(${time 2})) +> (extract_minutes(${time 1}) - extract_minutes(${time 2}))/60 +> (extract_seconds(${time 1}) - extract_seconds(${time 2}))/3600))> , abs(diff_minutes(${time 1},${time 2})/60)> )
Calculate table calculation for each time/minute/hour/second to achieve the difference
Ex: Second
if(${seconds_arrival}=0, concat(“”,“”), if(${seconds_arrival}=1, concat(${seconds_arrival}, " second "), concat(${seconds_arrival}, " seconds ")))
Second arrival:
case(> when(diff_days(${date 1}, ${date 2}) = 0,> floor(mod(abs(((extract_hours(${time 1}) - extract_hours(${time 2}))*3600) +> ((extract_minutes(${time 1}) - extract_minutes(${time 2}))*60)+> (extract_seconds(${time 1}) - extract_seconds(${time 2}))),60))),> floor(abs(mod(diff_seconds(${time 1},${time 2}),60)))> )