ruben gallagher

ruben gallagher

  • 2.1k
  • 66
  • 1.1k

How To Create Stored Procedures In sql server Using Loop

Jun 26 2017 5:04 AM
 

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

 

Answers (2)