CLIENT SUPPORT » Bank Mega

Declare @TableTampung TABLE (

ROW VArchar(20),

emp_id VArchar(20),

start_date VArchar(20),

end_date VArchar(20),

duration VArchar(20),

absence_type VArchar(20)

)

DECLARE @Counter INT 

SET @Counter=1

INSERT INTO @TableTampung

SELECT ROW_NUMBER() OVER(ORDER BY emp_id ASC) as Row,emp_id,start_date,end_date,duration,absence_type FROM hr_md_emp_md0018 WHERE duration IS NULL OR duration =''

WHILE ( @Counter <= (SELECT MAX(ROW) FROM @TableTampung))

BEGIN

UPDATE hr_md_emp_md0018 

SET duration=(SELECT  Count(*) FROM hr_tm_workschedule_calender 

WHERE day_type='WDAY'

AND datespecified >= (SELECT start_date FROM @TableTampung Where ROW = @Counter)

AND datespecified <=(SELECT end_date FROM @TableTampung Where ROW = @Counter)

AND workschedule_type='5D8H'

AND country_id='IDN'

)

WHERE emp_id=(SELECT emp_id FROM @TableTampung Where ROW = @Counter)

and start_date=(SELECT start_date FROM @TableTampung Where ROW = @Counter)

and end_date=(SELECT end_date FROM @TableTampung Where ROW = @Counter)

and absence_type=(SELECT absence_type FROM @TableTampung Where ROW = @Counter)

SET @Counter  = @Counter  + 1

END