Trying to grab the next Code in this dataset. As of now the insert statement is only grabbing the 1st one and not the next one. Here is my code:
Declare @ID int
Declare @NumOfSeq int
Declare @Code nvarchar(250)
create table #ID_TempTable (ID int)
insert into #ID_TempTable select distinct ID from STX_JJ_Keller_Training
WHILE @@rowcount > 0 -- Still have ID exist
BEGIN
SET ROWCOUNT 1;
select @ID = ID from #ID_TempTable
print 'ID equals ' + cast(@ID as varchar)
delete from #ID_TempTable where ID = @ID
if exists (select 1 from HRET_10_21 where HRCo = 2 and HRRef = @ID)
BEGIN
select @NumOfSeq = Count(1) from STRL_Training_Active where ID = @ID
Declare @cnt INT = 0;
Declare @maxSeq INT;
select @maxSeq = max(Seq) from HRET_10_21 where HRCo = 2 and HRRef = @ID
While @cnt < @NumOfSeq
BEGIN
set @cnt = @cnt + 1;
BEGIN TRANSACTION;
SET ROWCOUNT 1;
insert into
HRET_10_21
(HRCo,HRRef,Seq,TrainCode,[Status],[Date],CompleteDate,
[Type],DegreeYN,Cost,ReimbursedYN,Instructor1099YN,
VendorGroup,OSHAYN,MSHAYN,FirstAidYN,CPRYN,WorkRelatedYN)
select
2 as HRCo,
ID as HRRef,
@maxSeq + @cnt as Seq,
Code as TrainCode,
case when Completed is not null then 'C' else 'S' end as Status,
[Enrollment Date] as Date,
Completed as CompleteDate,
'T' as Type, 'N' as DegreeYN, 0 as Cost,
'N' as ReimbursedYN ,'N' AS Instructor1099YN,
1 as VendorGroup, 'N' AS OSHAYN,
'N' AS MSHAYN,'N' AS FirstAidYN,
'N' AS CPRYN, 'Y' AS WorkRelatedYN
from
STRL_Training_Active
where
ID = @ID
COMMIT;
END -- end of while
END -- end of if
else
BEGIN
select @NumOfSeq = Count(1) from STRL_Training_Active where ID = @ID
Declare @cnt2 INT = 0;
While @cnt2 < @NumOfSeq
BEGIN
set @cnt2 = @cnt2 + 1;
BEGIN TRANSACTION;
SET ROWCOUNT 1;
insert into
HRET_10_21
(HRCo,HRRef,Seq,TrainCode,[Status],[Date],CompleteDate,
[Type],DegreeYN,Cost,ReimbursedYN,Instructor1099YN,
VendorGroup,OSHAYN,MSHAYN,FirstAidYN,CPRYN,WorkRelatedYN)
select
HRCo,
ID as HRRef,
@cnt2 as Seq,
Code as TrainCode,
case when Completed is not null then 'C' else 'S' end as Status,
[Enrollment Date] as Date,
Completed as CompleteDate,
'T' as Type, 'N' as DegreeYN, 0 as Cost,
'N' as ReimbursedYN ,'N' AS Instructor1099YN,
1 as VendorGroup, 'N' AS OSHAYN,
'N' AS MSHAYN,'N' AS FirstAidYN,
'N' AS CPRYN, 'Y' AS WorkRelatedYN
from
STRL_Training_Active
where
ID = @ID
COMMIT;
END -- end of while
END -- end of else
--select 1 from #ID_TempTable
END
drop table #ID_TempTable
