Try below:
Declare @LeaveAttendence as table(Id int identity(1,1), RegNo nvarchar(7), LeaveStartDate Date, LeaveEndDate Date, ContinuousLeave int)
Declare @incrementCount int
Declare @totalCount int
Select @totalCount = Count(*) from Attendence
Select @incrementCount = Min(attid) from Attendence
Declare @RegNo nvarchar(7), @LeaveStartDate Date, @LeaveEndDate Date, @Attendance char(1)
while @incrementCount < @totalCount
Begin
Select @RegNo = RegNo, @LeaveStartDate = Attdate, @LeaveEndDate = Attdate, @Attendance = Attendance from Attendence where attid = @incrementCount
If Not Exists(Select 1 from @LeaveAttendence where @RegNo = RegNo AND ContinuousLeave < 5)
Begin
Insert into @LeaveAttendence(RegNo,LeaveStartDate,LeaveEndDate,ContinuousLeave) values (@RegNo,null,null,0)
End
if @Attendance = 'A'
Begin
print Cast(@RegNo as varchar) + ' - ' + Cast(@LeaveEndDate as varchar)
If Exists(Select 1 from @LeaveAttendence where @RegNo = RegNo AND ContinuousLeave > 0)
Begin
Update @LeaveAttendence set LeaveEndDate = @LeaveEndDate,ContinuousLeave = ContinuousLeave + 1 where @RegNo = RegNo and ContinuousLeave < 5
End
Else
Begin
Update @LeaveAttendence set LeaveStartDate = @LeaveStartDate, LeaveEndDate = @LeaveEndDate,ContinuousLeave = 1 where @RegNo = RegNo and ContinuousLeave < 5
End
End
Else
Begin
Update @LeaveAttendence set LeaveStartDate = null, LeaveEndDate = null,ContinuousLeave = 0 where @RegNo = RegNo and ContinuousLeave < 5
End
SET @incrementCount = @incrementCount + 1
END
Select * from @LeaveAttendence where ContinuousLeave > 4