Help me with a code, please

Bouben · #1 (**permalink**) 12-06-11, 09:22

Hello, guys! (excuse my English, I am Czech)

Here is a problem I have.

The table 1 called Employees contains following columns filled with data about employees:

ID, First_name, Last_name, Day_of_birth, Date_of_commencement_of_employment

The table 2 called Sallary contains only following empty columns:

ID, Employee_ID, Month_of_payment, Gross_wage, Insurance, Net_wage

The code should take an employee from the Employee table and calculate his sallary untill October 2011. Then automatically take another employee from the table and calculate his sallary. This should continue

untill every employee's sallary is calculated.

Following code should explain you the plan:

DECLARE
@month varchar, @gross_w int, @insurance int, @net_w int, @Date_of_commencement datetime, @id int

-- here I am telling the program to select the data from the Employees table (problematic part, see below)

SELECT @Date_of_commencement = dbo.Employees.@Date_of_commencement FROM dbo.Employees

-- this part is checking when an employee started to work for us and therefore for how many months of work he is supposed to be payed (we dont want to pay him for the whole year if he is working since june)

IF
year(@Date_of_commencement) = year(getdate())
BEGIN
SET @month = month(@Date_of_commencement)
END

ELSE
BEGIN
SET @month = 1
END

-- now the wage and insurance calculations

SET @gross_w = 10000
WHILE @month < 11
BEGIN
SET @insurance = @gross_w * 0.15
SET @net_w = @gross_w - @insurance

-- (problematic part, see explanation below)

INSERT Sallary
(Employee_ID, Month_of_payment, Gross_wage, Insurance, Net_wage)
VALUES (0, @month, @gross_w, @insurance, @net_w)

-- also, the gross_w should be automatically increased per 5% each month

SET @gross_w = @gross_w * 1.05
SET @month = @month + 1

-- we are ending the calculations in October

IF @month = 10

BREAK
ELSE
CONTINUE
END

Well and it does not work :-)

To be more specific:

It will calculate the payments correctly but it will always select only the last employee from the Employee table even if you set Employee_ID to an exact number in the "INSERT Sallary" part of the code.

And because I am unable to find out where the problem is I am asking you for help.

This is a code my tutor has made in Oracle and it looks much more simple than mine (ignore the random wage number function in his code). Tell me, is it possible to make it that simple in SQL Server 2005? And

will the code for Oracle be actually functional or is it just an incomplete sketch my tutor has made? Thank you.

procedure sallary

declare
n_month numeric;
n_gross_wage numeric;
n_insurance numeric;
n_net_wage numeric;

BEGIN;

FOR Emp IN (SELECT * FROM Employee)
LOOP
IF year(Emp.Date_of_commencement) = year(sysdate) THEN
n_month := month(Emp.Date_of_commencement);
ELSE
n_month := 1;
END IF;
n_gross_wage := random_number(); -- this is the function to be ignored for our case

WHILE n_month <= 11
LOOP
n_insurance := n_gross_wage * 0.15;
n_net_wage := n_gross_wage - n_insurance;

INSERT INTO TABLE Sallary (0, Employee_ID, n_month, n_gross_wage, n_insurance, n_net_wage); -- the zero is supposed to be an automatically set Primary Key number

n_gross_wage := n_gross_wage * 0.01;
n_month := n_month + 1;
END LOOP;
COMMIT;

END LOOP;

END PROCEDURE;

Thank you guys!

Wim · #2 (**permalink**) 12-06-11, 12:16

It's still rough around the edges, but it should work:

Code:

DROP TABLE #Employees
CREATE TABLE #Employees(
	Id		INT	IDENTITY(1, 1)	NOT NULL, 
	First_name	VARCHAR(20)	NOT NULL,
	Last_name	VARCHAR(20)	NOT NULL,
	Day_of_birth	DATETIME	NOT NULL,
	Date_employment	DATETIME	NOT NULL,
	CONSTRAINT pk_Employees PRIMARY KEY (Id)
)

INSERT INTO #Employees(First_name, Last_name, Day_of_birth, Date_employment) VALUES
('First_name', 'Last_name', '2000-01-01', '2011-02-01'),
('Second_name', '2nd Last_name', '2002-02-02', '2010-11-01')

DROP TABLE #Salary
CREATE TABLE #Salary(
	Id		INT	IDENTITY(1, 1)	NOT NULL, 
	Employee_ID	INT	NOT NULL,
	Month_of_payment	INT	NOT NULL, 
	Year_of_payment	INT	NOT NULL, 
	Gross_wage	DEC(10, 2)	NOT NULL,
	Insurance	DEC(10, 2)	NOT NULL,
	Net_wage	DEC(10, 2)	NOT NULL,
	CONSTRAINT pk_Salary PRIMARY KEY (Id)
)

DECLARE @Month_Of_Payment INT, @year_of_payment	INT, @firstOfMonthOfPayment DATETIME, @firstOfNextMonthOfPayment DATETIME

SET @firstOfMonthOfPayment = '2011-01-01'

WHILE Month(@firstOfMonthOfPayment) <= 10
BEGIN
	SET @firstOfNextMonthOfPayment = DATEADD(mm, 1, @firstOfMonthOfPayment)
	SET @Month_Of_Payment = month(@firstOfMonthOfPayment)
	SET @year_of_payment = year(@firstOfMonthOfPayment)

	INSERT INTO #Salary(Employee_ID, Month_of_payment, Year_of_payment, Gross_wage, Insurance, Net_wage)
	SELECT E.ID,
		@Month_Of_Payment,
		@year_of_payment,
		COALESCE(S.Gross_wage * 1.05, 10000) as Gross_wage,
		COALESCE(S.Gross_wage * 1.05, 10000) * 0.15 as Insurance,
		COALESCE(S.Gross_wage * 1.05, 10000) * 0.85 as Net_wage 
	FROM #Employees as E
		LEFT OUTER JOIN #Salary as S ON
			E.ID = S.Employee_ID AND
			S.Month_of_payment = @Month_Of_Payment - 1 AND
			S.Year_of_payment = @year_of_payment
	WHERE Date_employment <= @firstOfMonthOfPayment AND
		Date_employment < @firstOfNextMonthOfPayment
			
	SET @firstOfMonthOfPayment = @firstOfNextMonthOfPayment
END

SELECT * FROM #Salary

About me doing your homework for you, I accept koruna, euros and dollars.

Bouben · #3 (**permalink**) 12-06-11, 16:52

Thank you! It really works nicely. But I have a problem with it.

I need to fill your code with something that will give me this:

-- this part is checking when an employee started to work for us and therefore for how many months of work he is supposed to be payed (we dont want to pay him for the whole year if he is working since june)

IF
year(@Date_of_commencement) = year(getdate())
BEGIN
SET @month = month(@Date_of_commencement)
END

ELSE
BEGIN
SET @month = 1
END

An employee should not be paid for months when he was not in a company.

Wim · #4 (**permalink**) 12-06-11, 19:39

Quote:

An employee should not be paid for months when he was not in a company.

Will he be paid in that case with the above code? Have you checked that?

'First_name' starts to work at 2011-02-01. Does Salary contains a record with person_ID 1 and month 1 and year 2011?

This whole assignment is flawed. I made a number of assumptions, like everybody gets hired on the first of the month and works during the whole month.

Bouben · #5 (**permalink**) 12-07-11, 05:40

Quote:

Originally Posted by Wim

Will he be paid in that case with the above code? Have you checked that?

'First_name' starts to work at 2011-02-01. Does Salary contains a record with person_ID 1 and month 1 and year 2011?

This whole assignment is flawed. I made a number of assumptions, like everybody gets hired on the first of the month and works during the whole month.

Well, what I know is that with your code the employee starting somewhere in the middle of 2011 was not counted at all by the code. I suppose that the problem is here:

Code:

SET @firstOfMonthOfPayment = '2011-01-01'

-- some code --

Code:

WHERE Date_employment <= @firstOfMonthOfPayment AND
		Date_employment < @firstOfNextMonthOfPayment

Anyway, the starting date of an employee should be checked automatically as in this part of my code:

Code:

IF
year(@Date_of_commencement) = year(getdate())
BEGIN
SET @month = month(@Date_of_commencement)
END

ELSE
BEGIN
SET @month = 1
END

Wim · #6 (**permalink**) 12-07-11, 08:01

Quote:

Well, what I know is that with your code the employee starting somewhere in the middle of 2011 was not counted at all by the code.

I have changed the sample data to:

Code:

INSERT INTO #Employees(First_name, Last_name, Day_of_birth, Date_employment) VALUES
('First_name', 'Last_name', '2000-01-01', '2011-06-01'),
('Second_name', '2nd Last_name', '2002-02-02', '2010-11-01')

This is what I get.

Code:

Id	EmpId Month	Yr	Gross		Ins	Net
1	2	1	2011	10000.00	1500.00	8500.00
2	2	2	2011	10500.00	1575.00	8925.00
3	2	3	2011	11025.00	1653.75	9371.25
4	2	4	2011	11576.25	1736.44	9839.81
5	2	5	2011	12155.06	1823.26	10331.80
6	1	6	2011	10000.00	1500.00	8500.00
7	2	6	2011	12762.81	1914.42	10848.39
8	1	7	2011	10500.00	1575.00	8925.00
9	2	7	2011	13400.95	2010.14	11390.81
10	1	8	2011	11025.00	1653.75	9371.25
11	2	8	2011	14071.00	2110.65	11960.35
12	1	9	2011	11576.25	1736.44	9839.81
13	2	9	2011	14774.55	2216.18	12558.37
14	1	10	2011	12155.06	1823.26	10331.80
15	2	10	2011	15513.28	2326.99	13186.29

The person 'First_name' gets his pay starting from June. While the second person gets paid from January. They both don't get paid when they don't work or worked in a year previous to 2011.

Quote:

the starting date of an employee should be checked automatically

That already happens. The second person stared to work in 2010, yet won't get paid for that period.

I honestly don't see why you think my solution is flawed about the periods when they should get pay.

(I did noticed a flaw in the calculation of Gross_wage, I have corrected it in my first post.)

Bouben · #7 (**permalink**) 12-07-11, 08:56

Man I swear I did not change a thing in your code and it suddenly works as you have written already.

But I absolutely don't get it. I swear it did not work. The employee starting in the middle of the year was not in the results at all, but now it is absolutely correctly calculated. I tried it many times and until now it did not work. My girlfriend working on the same task had absolutely identical results with me. But now it works for both of us. This is mysterious.

I don't take drugs or drink alcohol I don't know what is going on here...

Anyway it really works without problems.

Thank you very much. Now I am going to study your code in depth.

Wim · #8 (**permalink**) 12-07-11, 11:40

Quote:

Originally Posted by Bouben

Man I swear I did not change a thing in your code and it suddenly works as you have written already.

But I absolutely don't get it. I swear it did not work. The employee starting in the middle of the year was not in the results at all, but now it is absolutely correctly calculated. I tried it many times and until now it did not work. My girlfriend working on the same task had absolutely identical results with me. But now it works for both of us. This is mysterious.

I don't take drugs or drink alcohol I don't know what is going on here...

Weird.

I will rephrase my previous comment:

Quote:

About me doing your homework for you, I accept koruna, euros, dollars, ethanol, tobacco

I'm glad it works for you now.

Bouben · #9 (**permalink**) 12-07-11, 11:48

Ok, so I understand the code now but there is one mysterious thing to me.

This:

Code:

LEFT OUTER JOIN #Salary as S ON
			E.ID = S.Employee_ID AND
			S.Month_of_payment = @Month_Of_Payment - 1 AND
			S.Year_of_payment = @year_of_payment

What exactly is the - 1 value doing in the code.

The only thing I was able to deduce from it was a fact that without it or changed to + 1 the code will not increase the gross wage every month.

But it does not make any sense to me as the code is basically saying: go back one month. Especially when we are executing the loop for very first time it simply does not make any sense why would I want to go to the year 2010.

Anyway it seems to be crucial in the code so I need to know how exactly it works. It is the last piece of the jigsaw.

(I hope)

Thank you very much!

Wim · #10 (**permalink**) 12-07-11, 15:25

Quote:

Ok, so I understand the code now but there is one mysterious thing to me.

This:

Code:

LEFT OUTER JOIN #Salary as S ON
E.ID = S.Employee_ID AND
S.Month_of_payment = @Month_Of_Payment - 1 AND
S.Year_of_payment = @year_of_payment

What exactly is the - 1 value doing in the code.

Basically what the code does is

- Get an employee's gross_wage of the month preceding the month (and the year) we're calculating the pay cheques for.
- multiply it by 1.05 to get the new gross_wage of this month
- in case there is no previous gross_wage, take 10000 as value for the Gross_wage.
COALESCE(S.Gross_wage * 1.05, 10000) as Gross_wage,

To get the previous month's Gross_wage:
- we JOIN the Employee with his Salary for the previous month of the current year.
Say @Month_Of_Payment = 6, then the previous month = 6 - 1 = 5 (= @Month_Of_Payment - 1)
We can't use an INNER JOIN because there will be no previous month for the first month we have to pay an employee. That's why we have to use a LEFT OUTER JOIN (and a COALESCE to replace that non-existing Gross_wage value, by 10000)

Code:

LEFT OUTER JOIN #Salary as S ON
			E.ID = S.Employee_ID AND
			S.Month_of_payment = @Month_Of_Payment - 1 AND
			S.Year_of_payment = @year_of_payment

Please don't use this code in a production environment. The task description is so flawed (once someone gets hired we will pay him till the end of time, ... )... the solution will only work within the boundaries of the flawed task description.

Internet Services

Web Development

Digital Marketing

Consumer Tech