lundi 27 juin 2016

Why the cursor skip the first row

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