the single record that i used is table shipment_assignment or table a on this scheme
then i add alias (exit_time,enter_time, distance, duration, min_temp,max_temp,avg_temp) from process bellow
my goal is how to i made it with store proceduren in sql server 2014
i hope u can help me ...thanks
table a :
id vehicle_id begin_date_time o_id d_id end_date_time active
1 a 2017-06-17 00:00:00 10 11 ? 1
table b :
id name type
10 a a1
11 b a2
12 c a3
table c
id vehicle_id date_time event_type b_name b_type
1 a 2017-06-17 00:01:00 30 c a3
2 a 2017-06-17 00:02:00 31 a a1
3 a 2017-06:17 02:00:00 31 c a3
4. a 2017-06-17 04:00:00 30 b a2
table d
id vehicle_id date_time temp1
1 a 2017-06-17 00:01:00 -2
2 a 2017-06-17 00:02:00 1
3 a 2017-06-17 02:00:00 0
4 a 2017-06-17 04:00:00 1
table e
id vehicle_id start_date_time distance
1 a 2017-06-17 00:01:00 120
2 a 2017-06-17 00:04:00 300
3 a 2017-06-17 02:00:00 100
4 a 2017-06-17 04:00:00 50
so the result is on table a:
id : 1 (based input)
vehicle_id : a (based input)
begin_date_time : 2017-06-17 00:00:00 (based input)
o_id : 10 (based input)
d_id : 11 (based input)
end_date_time = 2017-06-17 04:00:00 on table c ,
(looping and search on table c , based begin_date_time with same origin_id (pool cikarang) with event_type 31 ,same destination_id (pool SAMB pulogebang) with event_type = 30
exit_time = 2017-06-17 00:02:00 on table c
enter_time = 2017-06-17 04:00:00 (same with end_date_time)
distance = 450 on table e
duration = difference begin_date_time and end_date_time on table a
min_temp = 0 on table d
max_temp = 1 on table d
avg_temp = 0.6 (round to 0) on table d
active = 0 (if end_date_time filled not null) on table a