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
Solved! Go to Solution.
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)))
)
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)))
)