-- i hava one temp table (#tempTbl) DECLARE @Sno INT DECLARE @Id INT DECLARE @TotalMinutes AS INTEGER=0 DECLARE @OTimeMinutes AS INTEGER=0 DECLARE @RUMinutes INTEGER=0 DECLARE sumcal CURSOR GLOBAL SCROLL STATIC OPTIMISTIC FOR SELECT Id,Sno FROM #tempTbl OPEN sumcal FETCH FIRST FROM sumcal INTO @Id,@Sno WHILE ( @@FETCH_STATUS = 0 ) BEGIN SELECT @RUMinutes = SUAM FROM #tempTbl WHERE SNo=@Sno SELECT @TotalMinutes = SUM(SUAM) FROM #tempTbl WHERE Id=@Id and SNo<=@Sno IF(@TotalMinutes>2400) BEGIN if (SELECT COUNT(*) FROM #tempTbl WHERE Id=@Id AND SNo<@Sno AND OTM>0)>0 BEGIN SELECT @OTimeMinutes = SUAM FROM #tempTbl WHERE SNo=@Sno SET @RUMinutes = 0 END ELSE BEGIN SET @OTimeMinutes = (@TotalMinutes-2400) SET @RUMinutes = (@RUMinutes - @OTimeMinutes) END END ELSE BEGIN SET @OTimeMinutes = 0 END UPDATE #tempTbl SET OTM = @OTimeMinutes, RSUM = @RUMinutes WHERE SNo=@Sno SET @OTimeMinutes = 0 SET @TotalMinutes = 0 SET @RUMinutes = 0 FETCH NEXT FROM sumcal INTO @Id,@Sno END CLOSE sumcal DEALLOCATE sumcal
#tempTbl table : SNo Id SUAM OTM RSUM 1 254 480 0 0 2 254 480 0 0 3 254 480 0 0 4 254 480 0 0 5 254 480 0 0 6 254 480 0 0 7 277 480 0 0 8 277 480 0 0 9 277 480 0 0 10 277 480 0 0
output SNo Id SUAM OTM RSUM 1 254 480 0 480 2 254 480 0 480 3 254 480 0 480 4 254 480 0 480 5 254 480 0 480 6 254 480 480 0 7 277 480 0 480 8 277 480 0 480 9 277 480 0 480 10 277 480 0 480
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)