SP example

 

DECLARE @CursorEmployeePositionContent CURSOR

DECLARE @CursorMovementPositionContent CURSOR

DECLARE @EmployeePositionId INT

DECLARE @MovementType2EmployeeId INT

DECLARE @MovementType2PositionId  INT

DECLARE @MovementType2IsPrimaryPosition  INT

DECLARE @MovementType2JobCodeId  INT

--Update movementType 1

SET @CursorEmployeePositionContent = CURSOR FORWARD_ONLY READ_ONLY FOR 

select EmployeePositionId from EmployeePosition where 1=1 

and exists (select 1 from MovementPosition 

where MovementPosition.EmployeeId = EmployeePosition.EmployeeId

and MovementPosition.PositionId = EmployeePosition.PositionId 

and MovementPosition.MovementType = 1

and MovementPosition.EmployeeId = 18449

and MovementPosition.movementId = 7)

OPEN @CursorEmployeePositionContent

FETCH NEXT FROM @CursorEmployeePositionContent INTO 

@EmployeePositionId  

WHILE @@FETCH_STATUS = 0 

BEGIN 

Update EmployeePosition set

TerminationDate = (select TerminationDate from MovementPosition 

where MovementPosition.EmployeePositionId = @EmployeePositionId

and MovementPosition.InactiveTime is null

)

where EmployeePosition.EmployeePositionId = @EmployeePositionId

 

  FETCH NEXT FROM @CursorEmployeePositionContent INTO 

@EmployeePositionId

END

CLOSE @CursorEmployeePositionContent

DEALLOCATE @CursorEmployeePositionContent

--insert movementType 2

SET @CursorMovementPositionContent = CURSOR FORWARD_ONLY READ_ONLY FOR 

select EmployeeId, PositionId, IsPrimaryPosition, JobCodeId from MovementPosition 

where 1=1

and MovementPosition.InactiveTime is null

and MovementPosition.MovementType = 2

and MovementPosition.EmployeeId = 18449

and MovementPosition.movementId = 7

OPEN @CursorMovementPositionContent

FETCH NEXT FROM @CursorMovementPositionContent INTO 

@MovementType2EmployeeId,

@MovementType2PositionId,

@MovementType2IsPrimaryPosition,

@MovementType2JobCodeId

WHILE @@FETCH_STATUS = 0 

BEGIN 

insert into EmployeePosition

select @MovementType2EmployeeId, @MovementType2PositionId, @MovementType2IsPrimaryPosition, @MovementType2JobCodeId, WorkingHoursPerDay, WorkingDaysPerWeek, WorkingDaysPerYear, WeeklyWorkingHours, RequireShiftDuty, AltSat, 

TotalSalary, TotalAllowance, BonusPercentage, BonusAmount, MealCodeId, JoinDate, JoinCurrentPositionCategoryDate, null, NextApprasialDate, NextSalaryReviewDate, ADAccount, CompanyEmail, 

getdate(), LastUpdateEmployeeId, getdate(), CreateEmployeeId, InactiveTime, InactiveEmployeeId, OnboardCheckedByPositionId, OnboardCheckedByEmployeeId, AdminOfficerOnboardCheckedTime, 

EmployeeOnboardCheckedTime

from EmployeePosition

where EmployeePositionId = (select top 1 EmployeePositionId  from EmployeePosition where IsPrimaryPosition = 1 

and EmployeeId = 18449 and EmployeePositionId = 609 

order by JoinCurrentPositionCategoryDate desc)

FETCH NEXT FROM @CursorMovementPositionContent INTO 

@MovementType2EmployeeId,

@MovementType2PositionId,

@MovementType2IsPrimaryPosition,

@MovementType2JobCodeId

END

CLOSE @CursorMovementPositionContent

DEALLOCATE @CursorMovementPositionContent