In a stored procedure, I have to do some calculation based on the previous record and a total. Currently I can achieve this using Cursor or a table variable, but its taking a lot of time as the number of records are huge.
How can I achieve this to improve the performance?
A part of my stored procedure:
DECLARE @HoursTemp TABLE ( ID [INT], EmployeeName [VARCHAR](100), WorkDate [DATE], RegularHours [DECIMAL], OvertimeHours [DECIMAL], NightJob [BIT], JobId [INT] ) INSERT INTO @HoursTemp SELECT ID, EmployeeName, WorkDate, RegularHours,OvertimeHours, NightJob, JobId FROM @PayrollSheet GROUP BY WorkDate, EmployeeName , JobIdType, NightJob, RegularHours, OvertimeHours, ID, JobId ORDER BY WorkDate, EmployeeName, JobIdType, NightJob, JobId SELECT @count = COUNT(ID) FROM @HoursTemp WHILE (@i <= @count) BEGIN Print @i SELECT @PayRollID = ID , @EmployeeName = EmployeeName, @WorkDate = WorkDate, @RegularHours = RegularHours, @OverTimeHour = OvertimeHours, @NightHours = NightJob, @PayRollJobID = JobId FROM @HoursTemp IF(@WorkDate = @PreviousDate AND @EmployeeName = @PreviousEmployeeName) -- If Current date and previous date matches BEGIN SET @SumOfHours = @SumOfHours + @RegularHours; -- Then Sum up the regular hour with previous hour. IF(@SumOfHours >= 8 AND @SumOverTimeHours = 0) BEGIN SET @SumOverTimeHours = @OverTimeHour + (@SumOfHours - 8); -- Sum up the overtime hour. SET @RegularHours = 8 - (@SumOfHours - @RegularHours); -- Get remainder regular hour. UPDATE @PayrollSheet SET RegularHours = @RegularHours, OvertimeHours = @SumOverTimeHours Where ID = @PayRollID -- Update the temp table. END ELSE BEGIN --When sum of regular hours is greater than 8, further regular hours of the day will be considered as over time hours. IF(@SumOfHours >=8) UPDATE @PayrollSheet SET RegularHours = 0, OvertimeHours = @RegularHours Where ID = @PayRollID --Update the over time hours with regular hour and set regular hour with 0. END END ELSE BEGIN SET @SumOfHours = @RegularHours; -- If Dates does not match then reset the regular hours sum variable to current regular hours so it will be summed with next regular hours when dates match. SET @SumOverTimeHours = 0; -- Sum up Over time hours present in existing records. --SET @SumOfNightHours = 0; -- Reset OverTime Sum and Night hours sum variable when dates does not match. END -- Get counts of record with same work date,Job ID and Night Job true and match it with total records with same work date. SELECT @RecordsWithWorkDateAndJobID = COUNT(*) from @PayrollSheet where WorkDate = @WorkDate AND JobID = @PayRollJobID AND NightJob = 1 AND EmployeeName = @EmployeeName SELECT @RecordsWithOnlyWorkDate = COUNT(*) from @PayrollSheet where WorkDate = @WorkDate AND EmployeeName = @EmployeeName --AND NightJob = 1 --If both count matches then night hours are entered in same job on a day. Else night hours are entered in more than one job. Also if sum of night hours is less than 8 then only update night hours. IF(NOT(@RecordsWithWorkDateAndJobID > 0 AND @RecordsWithOnlyWorkDate > 0 AND @RecordsWithWorkDateAndJobID = @RecordsWithOnlyWorkDate)) --AND @SumOfNightHours < 8 BEGIN UPDATE @PayrollSheet SET NightJob = 0 Where ID = @PayRollID -- Set regular hours as 0 and night hours as the regular hour. --SET @SumOfNightHours = @SumOfNightHours + @RegularHours; -- Maintain sum of night hours, so that if it is greater than 8 then night hours will not be added. END SET @PreviousDate = @WorkDate --Store previous work date. SET @PreviousJobID = @PayRollJobID -- Store previous job id. SET @PreviousEmployeeName = @EmployeeName -- Fetch next record from temp table. SELECT @i = @i + 1 END