Click here to Skip to main content
14,550,507 members
Rate this:
Please Sign up or sign in to vote.
See more:
Please i need help to resolve my cursor script. I have used the same cursor script several times and is working fine. But i do not understand this one why is not working this time. I want sum individual overtime within the period the user will provide and insert it into a new table. I have to multiply overtime by salary per hour rate from a different table. I have not added the function part of the code to sum the overtime value because is working and even if comment that part the rest of the code is not able to insert into the table and i do not get any error message. Below is the script. I be grateful to get help. Thanks in advance.

What I have tried:

This is what I have tried.
--Tables
Create Table EmployeeAttendanceRecords
(
       EmployeeID	     VarChar(10)Not Null,
       EmployeeName          VarChar(50)Not Null,
       OverTime              SmallInt,
       WorkingDate           DateTime
)

Create Table EmployeeDetails
(
    EmployeeID        VarChar(10),
    EmployeeName      VarChar(10),
    SalaryPerHour     Money
)

Create Table TestOverTime
(
	EmployeeID		VarChar(10)Not Null,
	EmployeeName		VarChar(50)Not Null,
	OverTime		Money
)

--Sample Data For Test
EMP001,John,1,01 JAN 2020
EMP002,Linda,1,01 JAN 2020
EMP003,Mark,2,01 JAN 2020

EMP001,John,1,02 JAN 2020
EMP002,Linda,2,02 JAN 2020
EMP003,Mark,1,02 JAN 2020

EMP001,John,1,03 JAN 2020
EMP002,Linda,1,03 JAN 2020
EMP003,Mark,1,03 JAN 2020

--Salary Per Hour
John  = 30
Linda = 25
Mark  = 27


--Stored Produre
Create Proc prcTestOverTime
(
        @StartDate		DateTime,
	@EndDate		DateTime
)
As
Begin
	Declare @OverTimeAmount		SmallInt,
		@EmployeeID		VarChar(10)

Begin
	Declare Cur Cursor For
		
	Select EmployeeID From EmployeeDetails
	Where EmployeeID = @EmployeeID
		
Open Cur
Fetch Next From Cur Into @EmployeeID
While @@Fetch_Status = 0
Begin
Select @OverTimeAmount = dbo.fnTestCalculateOverTime(@EmployeeID,@StartDate,@EndDate)
			
	Insert Into TestOverTime

	Select EmployeeID,
		EmployeeName,
		@OverTimeAmount
	From EmployeeAttendanceRecords
	Where EmployeeID = @EmployeeID
        And WorkingDate Between @StartDate And @EndDate

	Fetch Next From Cur Into @EmployeeID
End
Close Cur
Deallocate Cur;
End
End
Posted
Updated 25-May-20 21:06pm
v4

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Take a look at below example:

DECLARE @EmployeeAttendanceRecords Table (EmployeeID VarChar(10)Not Null, EmployeeName VarChar(50)Not Null, OverTime SmallInt, WorkingDate DateTime)
DECLARE @EmployeeDetails Table (EmployeeID VarChar(10), EmployeeName VarChar(10), SalaryPerHour Money)

--Sample Data For Test
INSERT INTO @EmployeeAttendanceRecords(EmployeeID, EmployeeName, OverTime, WorkingDate)
VALUES('EMP001' , 'John',1 ,'2020-01-01'),
('EMP002' , 'Linda', 1 ,'2020-01-01'),
('EMP003' , 'Mark', 2 ,'2020-01-01'),
('EMP001' , 'John', 1 ,'2020-01-02'),
('EMP002' , 'Linda', 2 ,'2020-01-02'),
('EMP003' , 'Mark', 1 ,'2020-01-02'),
('EMP001' , 'John', 1 ,'2020-01-03'),
('EMP002' , 'Linda', 1 ,'2020-01-03'),
('EMP003' , 'Mark', 1 ,'2020-01-03')


INSERT INTO @EmployeeDetails(EmployeeID, EmployeeName, SalaryPerHour)
VALUES('EMP001' , 'John', 30),
('EMP002' , 'Linda', 25),
('EMP003' , 'Mark', 27)


SELECT ear.EmployeeID, ear.EmployeeName, ear.TotalOT * ed.SalaryPerHour AS TotalSalary
	--, ear.WorkingMonth
FROM (
	SELECT EmployeeID, EmployeeName
	--, CONVERT(DATE, DATEADD(DD, -DAY(WorkingDate), WorkingDate) +1) WorkingMonth
	, SUM(OverTime) TotalOT 
	FROM @EmployeeAttendanceRecords 
	GROUP BY EmployeeID, EmployeeName
	--, DATEADD(DD, -DAY(WorkingDate), WorkingDate) +1
	--INFO: uncomment above lines to get mothly salary
) ear INNER JOIN @EmployeeDetails ed ON ear.EmployeeID = ed.EmployeeID


What i'm trying to say is: you don't need cursor for such of requirement. Now, you can simply use INSERT INTO statement together with SELECT.

[EDIT]

Quote:
Am still facing some challenges in calculation salary per hour. In the first values i used in the code were for example purpose. For example one may have 5000 as a basic salary for a month and i need per hour value for that figure to work out on the overtime issue.


In this case EmployeeDetails table have to refer to another table which holds information about how many hours is needed for daily or monthly salary.
See:
DECLARE @EmployeeAttendanceRecords Table (EmployeeID VarChar(10)Not Null, EmployeeName VarChar(50)Not Null, OverTime SmallInt, WorkingDate DateTime)
DECLARE @EmployeeDetails Table (EmployeeID VarChar(10), EmployeeName VarChar(10), Salary Money, EwtID INT)
DECLARE @ExpectedWorkingTime TABLE(EwtID INT IDENTITY(1,1), EwtDescription VARCHAR(150), EwtHours INT)

--helper table!
INSERT INTO @ExpectedWorkingTime (EwtDescription, EwtHours)
VALUES('Hour', 1), ('Daily', 8), ('Monthly', 28*8)
--if employee salary is per hour divide it by 1
--if employee salary is daily divide it by 8
--if employee salary is monthly divide it by 28*8

--Sample Data For Test
INSERT INTO @EmployeeAttendanceRecords(EmployeeID, EmployeeName, OverTime, WorkingDate)
VALUES('EMP001' , 'John',1 ,'2020-01-01'),
('EMP002' , 'Linda', 1 ,'2020-01-01'),
('EMP003' , 'Mark', 2 ,'2020-01-01'),
('EMP001' , 'John', 1 ,'2020-01-02'),
('EMP002' , 'Linda', 2 ,'2020-01-02'),
('EMP003' , 'Mark', 1 ,'2020-01-02'),
('EMP001' , 'John', 1 ,'2020-01-03'),
('EMP002' , 'Linda', 1 ,'2020-01-03'),
('EMP003' , 'Mark', 1 ,'2020-01-03')


INSERT INTO @EmployeeDetails(EmployeeID, EmployeeName, Salary, EwtID)
VALUES('EMP001' , 'John', 6720, 3),
('EMP002' , 'Linda', 200, 2),
('EMP003' , 'Mark', 27, 1)


SELECT ear.EmployeeID, ear.EmployeeName 
	--, ed.Salary, ewt.EwtDescription , ear.TotalOT , ewt.EwtHours
	, ear.TotalOT * (ed.Salary / ewt.EwtHours) AS TotalSalary
	--, ear.WorkingMonth
FROM (
	SELECT EmployeeID, EmployeeName
	--, CONVERT(DATE, DATEADD(DD, -DAY(WorkingDate), WorkingDate) +1) WorkingMonth
	, SUM(OverTime) TotalOT 
	FROM @EmployeeAttendanceRecords 
	GROUP BY EmployeeID, EmployeeName
	--, DATEADD(DD, -DAY(WorkingDate), WorkingDate) +1
	--INFO: uncomment above lines to get mothly salary
) ear INNER JOIN @EmployeeDetails ed ON ear.EmployeeID = ed.EmployeeID
	INNER JOIN @ExpectedWorkingTime ewt ON ed.EwtID = ewt.EwtID

Result:
EmployeeID	EmployeeName	TotalSalary
EMP001	John	90,00
EMP002	Linda	100,00
EMP003	Mark	108,00
   
v3
Comments
NanaKwame 26-May-20 22:52pm
   
Thanks @Maciej Los for the support and most grateful for that. Am still facing some challenges in calculation salary per hour. In the first values i used in the code were for example purpose. For example one may have 5000 as a basic salary for a month and i need per hour value for that figure to work out on the overtime issue. I created a function which is working but because the cursor is not working i have to find different way of solving that. And this is what i have done but kind of stuck at the moment.

Declare @BasicSalaryDetails Table (EmployeeID VarChar(10),EmployeeName VarChar(50),BasicSalary Money)
Declare @MonthDays Table (MonthlyWorkingDays SmallInt)
Declare @DailyWorkingHours Table (ExpectedDailyWorkingHours SmallInt)

--Sample data to work with
--Number of days for the month
MonthlyWorkingDays = 28

--Hours for the day
ExpectedDailyWorkingHours = 8

insert into t_tmpEmployeeBasicSalary

Select MonthlyWorkingDays From MonthDays ma Union
Select da.ExpectedDailyWorkingHours * ma.MonthlyWorkingDays As MonthlyTotalHours From DailyWorkingHours da Union
(Select EmployeeID,EmployeeName,(BasicSalary/MonthlyTotalHours) As BasicSalaryPerHour From EmployeeDetails da
Group By EmployeeID,EmployeeName)

After getting the exact value per hour then I can use the solution you provided for me. Thanks.
Maciej Los 27-May-20 2:08am
   
Is there any tag which is used to define monthy/daily/per hour salary for specific employee?
NanaKwame 27-May-20 17:13pm
   
No sir I do not have any tag for specific employee. If you think is important to do that please kindly guide me on that. Thanks for been here for me.
Maciej Los 27-May-20 17:24pm
   
Please, see updated answer.
Maciej Los 27-May-20 4:02am
   
See updated answer.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100