Access previous records from a table variable without using cursor in SQL Server 2014

1 vote

1 answer

154 views

sql-server sql-server-2014 performance stored-procedures optimization query-performance

                          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 = 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
                        

Asked by Rishi Samadhiya (21 rep)

Feb 23, 2017, 11:46 AM
Last activity: Jul 18, 2025, 09:04 AM

Access previous records from a table variable without using cursor in SQL Server 2014

Related Questions