How To Create Stored Procedures In sql server Using Loop

ruben gallagher

9y906

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

ruben gallagher9y ago
hi nilesh

that's above my simple scheme table

Looping function to meet the end_date_time criteria in the table shipment assignment

i have to show the data with view on sql server 2014, but my goal is with store procedure

Because the end_date_time condition is met based on begin_date_time, o_id and d_id,
the condition will search to every table I mentioned and the red mark is the condition
that is achieved.
here's i gave my sample view
ALTER VIEW [dbo].[v_shipment_assignment ]
AS
SELECT sa.id, sa.shipment_no, sa.vehicle_id,sa.origin_id,sa.destination_id,f.geofence_name AS origin_name,g.geofence_name AS destination_name,f.geofence_type AS origin_type,g.geofence_type AS destination_type,
sa.create_date,sa.begin_date_time,sa.end_date_time,CONVERT(VARCHAR(5),
DATEDIFF(dd, sa.begin_date_time, sa.end_date_time)) + 'd ' + CONVERT(VARCHAR(5), DATEDIFF(hh, sa.begin_date_time, sa.end_date_time) % 24) + 'h ' + CONVERT(VARCHAR(5), DATEDIFF(mi,
sa.begin_date_time, sa.end_date_time) % 60) + 'm ' + CONVERT(VARCHAR(5), DATEDIFF(S, sa.begin_date_time, sa.end_date_time) % 60) + 's' AS total_duration,
p.min_temp,p.max_temp,p.average_temp,r.start_geofence,r.exit_time,z.end_geofence,z.enter_time,t.total_trips,sa.status,sa.active
FROM dbo.shipment_assignment AS sa LEFT OUTER JOIN
dbo.geofence AS f ON sa.origin_id = f.geofence_id LEFT OUTER JOIN
dbo.geofence AS g On sa.destination_id = g.geofence_id LEFT OUTER JOIN
dbo.vehicles AS v ON v.vehicle_id = sa.vehicle_id LEFT OUTER JOIN
dbo.members AS m ON sa.partner_id = m.member_id
Outer Apply (SELECT SUM(distance) AS total_trips
FROM dbo.trips
WHERE (vehicle_id = sa.vehicle_id) AND (start_date_time >= sa.begin_date_time) AND (start_date_time <= sa.end_date_time)
GROUP BY vehicle_id) AS t
Outer Apply (SELECT MIN(temp1) AS min_temp , MAX (temp1) AS max_temp , AVG(temp1) AS average_temp
FROM dbo.temperature_events
WHERE (vehicle_id = sa.vehicle_id) AND (date_time >= sa.begin_date_time) AND (date_time <= sa.end_date_time)
GROUP BY vehicle_id) AS p
Outer Apply (SELECT TOP (1) geofence_name AS start_geofence , date_time AS exit_time
FROM dbo.event_with_location
WHERE (vehicle_id = sa.vehicle_id) AND (date_time >= sa.begin_date_time) AND (date_time <= sa.end_date_time) AND (event_type = 31)
ORDER BY date_time Asc ) AS r
Outer Apply ( SELECT TOP (1) geofence_name AS end_geofence , date_time AS enter_time
FROM dbo.event_with_location
WHERE (vehicle_id = sa.vehicle_id) AND (date_time >= sa.begin_date_time) AND (date_time <= sa.end_date_time) AND (event_type = 30)
ORDER BY date_time Desc ) AS z
GO
Nilesh Shah9y ago
First of all I don't understand why you want to use a loop?

What have you tried till now can you post that? so that someone can answer you with some improvements?

You can use cursor in case when you want to loop thru a set of data.

Your table structure is not self explanatory so its difficult to understand the relationship among these tables.