Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    10

    Unanswered: Help me with a code, please

    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!
    Last edited by Bouben; 12-06-11 at 10:29.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    Last edited by Wim; 12-07-11 at 09:18. Reason: Corrected a flaw in the calculation of Gross_wage
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Nov 2011
    Posts
    10
    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.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2011
    Posts
    10
    Quote Originally Posted by Wim View Post
    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

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.

    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.)
    Last edited by Wim; 12-07-11 at 09:22.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Nov 2011
    Posts
    10

    Question Am I stoned?

    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.

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Bouben View Post
    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:

    About me doing your homework for you, I accept koruna, euros, dollars, ethanol, tobacco
    I'm glad it works for you now.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Nov 2011
    Posts
    10
    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!

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •