December 6, 2011 at 7:27 am
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!
December 6, 2011 at 7:51 am
For your assignment you need to replace the Oracle part
FOR Emp IN (SELECT * FROM Employee)
LOOP
WITH a CURSOR in SQL SERVER
DECLARE @EmpID INT;
DECLARE cur_Emp CURSOR FOR (SELECT Employee_ID FROM Employee)
OPEN cur_Emp
FETCH NEXT FROM cur_Emp INTO @EmpID
WHILE @@FETCH_STAUS = 0
BEGIN
... insert your loop on months here
... use @EmpID in place of your 0 in the INSERT INTO Salary
FETCH NEXT FROM cur_Emp INTO @EmpID
END
CLOSE Cur_Emp
DEALLOCATE Cur_Emp
However: Your tutor may be trying to make a point of how inefficient CURSORS and LOOPs are in SQL.
There will be a much simpler and faster way of doing this problem without using LOOPs. If you are interested I can sort out a few links to get you started.
I wait to be slated by my fellow SQL Server Dudes for daring to suggest a cursor. :hehe:
December 6, 2011 at 7:54 am
A couple of things. The month of payment, is that a date?
Also, where does the beginning wage come from? Is that supplied by you?
You don't have a SELECT that retrieves the ID from the employee table? That may be your problem.
In SQL Server, you generally don't want to do this with looping. While you might end up looping through the months, you certainly would want a set based solution that will calculate the salary for all employees for a month at once. To add an automatic PK to the Salary table, you would use an identity property on the column and leave that out of your insert.
As a sample, one way to calculate all the values for last month is like this:
DECLARE @currentMonth DATETIME
SET @currentMonth = '2/1/2011'
SELECT Employee_ID
, s.Gross_wage * 1.05 'salary'
, (s.Gross_wage * 1.05) * .15 'Insurance'
, (s.Gross_wage * 1.05) - ((s.Gross_wage * 1.05) * .15) 'Net'
FROM Salary s
INNER JOIN Employees e
ON e.ID = s.Employee_ID
AND e.Date_of_commencement_of_employment > @currentMonth
WHERE Month_of_payment = dateadd(month, -1, @currentMonth)
It's unclear what values are stored where or in what format. It will help if you provide some table definitions and sample data.
What you want to do is work with this as a SELECT first, getting the calculations correct before you start altering the table with inserts or updates.
December 6, 2011 at 9:39 am
Tom Brown (12/6/2011)
There will be a much simpler and faster way of doing this problem without using LOOPs. If you are interested I can sort out a few links to get you started.
I definitely am interested!
Thank you!
Actually, I tried the cursor way but did not polish it yet so it is not functional yet. And yes, I've read that cursors are resource heavy.
December 6, 2011 at 10:04 am
Steve Jones - SSC Editor (12/6/2011)
A couple of things. The month of payment, is that a date?
No, actually it is set as a varchar(50) column. Should I change it to a date data type?
Also, where does the beginning wage come from? Is that supplied by you?
Yes, it is supplied by me in this part of code: SET @gross_w = 10000
As a sample, one way to calculate all the values for last month is like this:
DECLARE @currentMonth DATETIME
SET @currentMonth = '2/1/2011'
SELECT Employee_ID
, s.Gross_wage * 1.05 'salary'
, (s.Gross_wage * 1.05) * .15 'Insurance'
, (s.Gross_wage * 1.05) - ((s.Gross_wage * 1.05) * .15) 'Net'
FROM Salary s
INNER JOIN Employees e
ON e.ID = s.Employee_ID
AND e.Date_of_commencement_of_employment > @currentMonth
WHERE Month_of_payment = dateadd(month, -1, @currentMonth)
Thank you very much! I am going to check it out. But what does the -1 means in this part of the code? WHERE Month_of_payment = dateadd(month, -1, @currentMonth)
What you want to do is work with this as a SELECT first, getting the calculations correct before you start altering the table with inserts or updates.
Ok. So if I calculate all the stuff at once would it work even if 1 of 10 employees have only 6 months to be paid for? What about the first 6 months? Will there be NULLs in columns correctly?
December 6, 2011 at 10:48 am
If you need nulls or 0s in there, then you would add that logic, and replace the values in the SELECT with a NULL based on conditions.
You haven't spelled out all the logic. As I mentioned, please create some sample test data and table DDL to help us understand what your requirements are.
December 6, 2011 at 10:52 am
Steve Jones - SSC Editor (12/6/2011)
If you need nulls or 0s in there, then you would add that logic, and replace the values in the SELECT with a NULL based on conditions.You haven't spelled out all the logic. As I mentioned, please create some sample test data and table DDL to help us understand what your requirements are.
Well, I have written this. Don't know if it is clear enough but this is what I need:
-- 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
December 7, 2011 at 2:08 am
Bouben (12/6/2011)
Tom Brown (12/6/2011)
There will be a much simpler and faster way of doing this problem without using LOOPs. If you are interested I can sort out a few links to get you started.I definitely am interested!
Thank you!
Actually, I tried the cursor way but did not polish it yet so it is not functional yet. And yes, I've read that cursors are resource heavy.
Steve Jones has already shown how you can link all employees to avoid a loop around employees. You can use a Tally Table to link on Months to avoid the inner loop. See Jeff Moden's Tally Table Article http://www.sqlservercentral.com/articles/T-SQL/62867/
To replace with a Tally Table in your case (I can't be 100% sure as Steve Suggests you need to post full CREATE TABLE statements and some sample data for a better answer)
DECLARE @startMonth DATETIME;
DECLARE @gross_w MONEY;
SET @startMonth = '2011-01-01';
SET @gross_w = 10000;
INSERT INTO Salary (EmployeeID, Month, salary, insurance, net)
SELECT Employee_ID
, T.N 'Month'
, @gross_w * 1.05 'salary'
, (@gross_w * 1.05) * .15 'Insurance'
, (@gross_w * 1.05) - ((@gross_w * 1.05) * .15) 'Net'
FROM Employees e
CROSS JOIN Tally T
WHERE e.Date_of_commencement_of_employment < dateadd(month, +T.N, @startMonth)
AND T.N < 10;
CROSS JOIN Tally means we get a row from create a Salary row for every row in the Tally Table. And we limit this to months 1-10 by using AND T.N < 10 in the WHERE clause - so every employee gets 10 months salary.
Also we limit employees salary by their start date, so employees starting after Jan 1st dont get counted until their starting month.
Note: This assumes your tally table starts at zero, if you start it at 1, you will need @startMonth to be '2010-12-01' AND T.N < 11
December 7, 2011 at 3:57 am
I need to understand two things in this part of the code. What does those red expressions in quotes mean and what does T.N mean? Probably stupid questions but I am a rookie. Thank you for your patience so far!
SELECT Employee_ID
, T.N 'Month'
, @gross_w * 1.05 'salary'
, (@gross_w * 1.05) * .15 'Insurance'
, (@gross_w * 1.05) - ((@gross_w * 1.05) * .15) 'Net'
December 7, 2011 at 4:09 am
Bouben (12/7/2011)
I need to understand two things in this part of the code. What does those red expressions in quotes mean and what does T.N mean? Probably stupid questions but I am a rookie. Thank you for your patience so far!
SELECT Employee_ID, T.N 'Month'
, @gross_w * 1.05 'salary'
, (@gross_w * 1.05) * .15 'Insurance'
, (@gross_w * 1.05) - ((@gross_w * 1.05) * .15) 'Net'
DECLARE @startMonth DATETIME;
DECLARE @gross_w MONEY;
SET @startMonth = '2011-01-01';
SET @gross_w = 10000;
INSERT INTO Salary (EmployeeID, Month, salary, insurance, net)
SELECT Employee_ID
, T.N 'Month'
, @gross_w * 1.05 'salary' -- Column Alias
, (@gross_w * 1.05) * .15 'Insurance'
, (@gross_w * 1.05) - ((@gross_w * 1.05) * .15) 'Net'
FROM Employees e
CROSS JOIN Tally T -- Table Alias
WHERE e.Date_of_commencement_of_employment < dateadd(month, +T.N, @startMonth)
AND T.N < 10;
Actually speaking, these questions are really basic and now I am thinking 'Will you understand Tally (given suggestion)?’
Please revisit following... SELECT (Transact-SQL)
December 7, 2011 at 4:15 am
Bouben (12/7/2011)
I need to understand two things in this part of the code. What does those red expressions in quotes mean and what does T.N mean? Probably stupid questions but I am a rookie. Thank you for your patience so far!
SELECT Employee_ID, T.N 'Month'
, @gross_w * 1.05 'salary'
, (@gross_w * 1.05) * .15 'Insurance'
, (@gross_w * 1.05) - ((@gross_w * 1.05) * .15) 'Net'
Its a way of naming the column in the result set. there are several ways of doing this.
SELECT Column1 AS MyColumn;
SELECT Column1 'MyColumn';
SELECT Column1 MyColumn;
In the example we use it to show which value is being calculated on that line.
T.N refers to the Tally Table value - did you read the article on tally tables?
T is the alias for the table Tally in CROSS JOIN Tally AS T
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply