declare @empno varchar(12)
declare @date date
set @date = '2021-3-31'
declare curEmp Cursor for select empno from employeemaster where active='Y'
open curEmp
fetch next from curEmp into @empno
while(@@fetch_status=0)
begin
update employeeservicehistory set NoOfChildB18 = (select top 1 NoOfChildB18 from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfChildA18 = (select top 1 NoOfChildA18 from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfDisChild = (select top 1 NoOfDisChild from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfDisChildA18 = (select top 1 NoOfDisChildA18 from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfDisChildA18b = (select top 1 NoOfDisChildA18b from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfChildTax = (select top 1 NoOfChildTax from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfChildB18 = (select top 1 NoOfChildB18 from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfChildB18b = (select top 1 NoOfChildB18b from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfDisChildb = (select top 1 NoOfDisChildb from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfDisChildA18b = (select top 1 NoOfDisChildA18b from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set NoOfChild = (select top 1 NoOfChild from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set TotalChildRelief = (select top 1 TotalChildRelief from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
update employeeservicehistory set TotalChild = (select top 1 TotalChild from EmployeeServiceHistory where effectivedate <=@date and empno=@empno) where effectivedate>=@date and recordtype in (select RecordType from EmployeeServiceRecordType where GroupType='salary') and empno=@empno
fetch next from curEmp into @empno
end
close curEmp
deallocate curEmp