Why the following Cursor skip the first Row !
OPEN lcr_trans
FETCH NEXT FROM lcr_trans INTO @trans_time , @machine_id , @trans_camp_code;
WHILE @@FETCH_STATUS = 0
BEGIN
if @get_transition_flag = 0 -- first -> in , last -> out --old
begin
if @trans_in is null
begin
set @trans_in = @trans_time;
insert into calc_md(userid,trans_date,transtime_in,in_machine_id,in_camp_code,
sch_id,att_id,att_type,att_start_time,att_end_time,
att_late_min,att_early_min,att_start_day,dep_code,emp_num,day_desc,day_flag,calc_date,hrworkhours)
values( @userid, @date, @trans_in, @machine_id, @trans_camp_code ,
@schid , @att_id,@att_type, @shift_start_time , @shift_end_time,
@shift_late_Minutes, @shift_early_Minutes,@dateStartTime,@dep_code,@emp_num, @dayDesc,@dayFlag,GETDATE(),@HRworkMinutes);
end
end
else --New
begin
if @trans_flag = 0
begin
set @trans_in = @trans_time;
insert into calc_md (userid,trans_date,transtime_in,in_machine_id,in_camp_code,
sch_id,att_id,att_type,att_start_time,att_end_time,
att_late_min,att_early_min,att_start_day,dep_code,emp_num,day_desc,day_flag,calc_date,hrworkhours)
values( @userid, @date, @trans_in, @machine_id, @trans_camp_code ,
@schid , @att_id,@att_type, @shift_start_time , @shift_end_time,
@shift_late_Minutes , @shift_early_Minutes , @dateStartTime , @dep_code,@emp_num, @dayDesc,@dayFlag,GETDATE(),@HRworkMinutes);
set @trans_flag = 1;
end
else
begin
update calc_md set transtime_out = @trans_time , out_machine_id = @machine_id , out_camp_code = @trans_camp_code
, calc_date = GETDATE(),hrworkhours=@HRworkMinutes
where userid = @userid and trans_date = @date and transtime_in = @trans_in ;
set @trans_flag = 0;
end
end
FETCH NEXT FROM lcr_trans INTO @trans_time , @machine_id , @trans_camp_code;
END
close lcr_trans;
DEALLOCATE lcr_trans;
My Data set Should have three rows :
2016-06-06 19:40:54.000 2 1
2016-06-06 23:39:50.000 3 1
2016-06-07 19:35:48.000 5 2
But the first row is skipped.
EDIT: My Cursor Definition :
select x.CHECKTIME , x.SENSORID , a.camp_code from Trans x , Port a
where x.SENSORID = a.TermID and x.USERID = @userid and (x.modified <> 2 or x.modified is null)
and ((x.belong_day is null and (( CONVERT(date,x.CHECKTIME) = @date and convert(time, x.CHECKTIME) > convert(time, @dateStartTime))
or ( CONVERT(date,x.CHECKTIME) = DATEADD(day, 1, @date) and convert(time, x.CHECKTIME) < convert(time, @dateStartTime))))
or (x.belong_day is not null and x.belong_day = @date))
and abs(DATEDIFF(minute, x.CHECKTIME , (select isnull(max(z.CHECKTIME),'1900-1-1') from Trans z
where z.USERID = x.USERID and ((z.CHECKTIME < x.CHECKTIME) and (z.modified <> 2 or z.modified is null) ) ))) > 2
order by 1 ;
Aucun commentaire:
Enregistrer un commentaire