hi indresh
i think i have found the code,
finnaly i have loop and match in another table
thanks for ur maximum attention
regards
ruben
ALTER PROCEDURE [dbo].[sp_shipment_assignment]
AS
BEGIN
SET NOCOUNT ON;
declare @end_date_time as datetime = NULL,
@id as int = NULL,
@vehicle_id as int = NULL,
@begin_date_time as datetime = NULL,
@destination_id as int = NULL
declare @tmp_shipment as table
(
id int identity(1,1),
id_shipment int,
vehicle_id int,
origin_id int,
destination_id int,
begin_date_time datetime NULL,
end_date_time datetime NULL,
active bit,
origin_exit_time datetime NULL
)
insert into @tmp_shipment
(
id_shipment,
vehicle_id,
origin_id,
destination_id,
begin_date_time,
end_date_time,
active,
origin_exit_time
)
select a.id, a.vehicle_id, a.origin_id, a.destination_id, a.begin_date_time, a.end_date_time,a.active, b.date_time
from shipment_assignment a
outer apply(
select top 1 date_time
from event_with_location
where vehicle_id = a.vehicle_id and event_type = 31
and object_id = a.origin_id
and date_time > a.begin_date_time
) as b
where ISNULL(end_date_time,0) = 0 and ISNULL(b.date_time,0) <> 0
declare @maxcount int = 0
declare @iloop int = 1
select @maxcount=count(1) from @tmp_shipment
while @iloop <= @maxcount
begin
select top 1 @id = id_shipment, @vehicle_id=vehicle_id, @destination_id=destination_id, @begin_date_time=begin_date_time
from @tmp_shipment
order by id asc
select top 1 @end_date_time = date_time
from event_with_location
where vehicle_id = @vehicle_id and event_type = 30
and object_id = @destination_id
and date_time > @begin_date_time
if isnull(@end_date_time,'') <> ''
begin
update shipment_assignment set end_date_time = @end_date_time,
active = 0
where id = @id
end
set @iloop = @iloop + 1
end
END
