May 19, 2011 at 4:12 am
Hi All,
I tries the following by using cursors.. but cursors consume more memory is there any other way to do this..
I) Retrieve employee data without using stored procedure and temp tables, his department information, project details, total no of hours spent on each project and calculate the bonus amount should be paid for employee with the following criteria.
IF total no of hours spent on a project > 100 hours, bonus= 5 % of salary
IF total no of hours spent on a project > 150 hours, bonus=15 % of salary
IF total no of hours spent on a project > 200 hours, bonus= 20 % of salary
Thanks in Advance,
Omeswar Reddy
May 19, 2011 at 4:16 am
Would need much more information before it would be possible to help - is all the information in a single table or multiple tables etc etc
May 19, 2011 at 4:21 am
Information is from different tables....we have 3 tables like Emp, Dept and Projects
May 19, 2011 at 4:30 am
Again - without more information about your tables it is not possible to say...also, you original question is a little vauge and raises more questions...such as what bonus to apply if an employee has worked over 100 hours on project A and over 150 hours on project B ??
I guess the simple answer is you will have to write a sql query 😛
Something along the line of the below would give you a line for each project the employee work on...
Select EmpDetails
,DeptDetails
,ProjectDetails
from emp
inner join Dept
on emp.DeptKey = Dept.DepKey
inner join Projects
ono emp.empKey = Project.EmpKey
May 19, 2011 at 5:35 am
Why the restriction to not use a stored procedure? A stored procedure is just a wrapper on a query, and you're going to have to write a query no matter what.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 19, 2011 at 5:43 am
I dont know exactly why my manager is asking like that.. but he strictly told that do not use SPROCS, Cursors or Temp Tables...
I think my manager wants the solution in different ways..
May 19, 2011 at 5:56 am
I'm with Grant here... there's no way around writting a query for this. Once that's done you can use it any way you want.
Are you looking at total hours worked or hours for a single project. we need more details about that before we can start giving hints.
The only hint I have at this point is to tell you to read up on "case" in books online.
May 19, 2011 at 6:15 am
I am looking at total hours worked for each single project.
May 19, 2011 at 6:26 am
odanwada (5/19/2011)
I am looking at total hours worked for each single project.
Is the bonus affected only once per employee? Is it on the max # hours for a single project? Cause if not at 100 hours per project times 20 projects = 100% bonus :-). Where can I apply :w00t:?
May 19, 2011 at 8:53 am
Using stored procedures is generally better than ad-hoc SQL anyway in light of plan reusability, right?. Ad-hoc SQL can be parameterized sometimes, but not always.
May 20, 2011 at 2:54 am
odanwada (5/19/2011)
I dont know exactly why my manager is asking like that.. but he strictly told that do not use SPROCS, Cursors or Temp Tables...I think my manager wants the solution in different ways..
I think you need to ask your manager exactly what (s)he does want.
Neither you nor any of us has a crystal ball, and Microsoft has yet to release the mind reading plugin for Management Studio! 🙂
May 20, 2011 at 4:56 am
odanwada (5/19/2011)
Information is from different tables....we have 3 tables like Emp, Dept and Projects
can you give us relation between all 3 table?
May 20, 2011 at 5:06 am
odanwada (5/19/2011)
I dont know exactly why my manager is asking like that.. but he strictly told that do not use SPROCS, Cursors or Temp Tables...I think my manager wants the solution in different ways..
I don't know what kind of structure you have but following structure may be or may not be you have.
CREATE TABLE emp
(
Empid INT ,
EmpName VARCHAR(100) ,
EmpSalary DECIMAL(18, 2)
)
CREATE TABLE Dept
(
Deptid INT ,
DeptName VARCHAR(100)
)
CREATE TABLE Project
(
ProjectID INT ,
ProjectName VARCHAR(100) ,
EmpID INT ,
DeptID INT ,
Hrs INT,
)
Then you can write following Query to solve your problem.
SELECT e.Empid ,
e.EmpName ,
d.Deptid ,
d.DeptName ,
P.ProjectID ,
ProjectName ,
CASE WHEN p.hrs > 100 THEN e.EmpSalary * 0.05
WHEN p.hrs > 150 THEN e.EmpSalary * 0.15
WHEN p.hrs > 200 THEN e.EmpSalary * 0.20
END Bonus
FROM dbo.Project P
INNER JOIN Dept D ON P.DeptID = D.Deptid
INNER JOIN dbo.emp E ON e.Empid = p.EmpID
May 20, 2011 at 5:55 am
Aren't you missing a table to relate the hours to the project and employee?
IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
DROP TABLE #Employee
IF OBJECT_ID('tempdb..#Project') IS NOT NULL
DROP TABLE #Project
IF OBJECT_ID('tempdb..#ProjectEmployeeHours') IS NOT NULL
DROP TABLE #ProjectEmployeeHours
CREATE TABLE #Employee (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EmpoyeeName VARCHAR(50) NOT NULL
)
CREATE TABLE #Project (
ProjectID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
ProjectName VARCHAR(50) NOT NULL
)
CREATE TABLE #ProjectEmployeeHours
(
ProjectIDINT,
EmployeeIDINT,
TimeSpentDECIMAL(19,5)
)
INSERT #Employee
( EmpoyeeName )
SELECT 'Fred'
UNION
SELECT 'Barney'
INSERT #Project
( ProjectName )
SELECT 'Quarry Sandstone'
UNION ALL
SELECT 'Dig new hole'
INSERT #ProjectEmployeeHours
( ProjectID, EmployeeID, TimeSpent )
SELECT 1,1,40.25
UNION ALL
SELECT 1,1,10.5
UNION ALL
SELECT 1,1,4.2
UNION ALL
SELECT 1,1,21.2
UNION ALL
SELECT 1,1,31.2
UNION ALL
SELECT 1,2,37.5
UNION ALL
SELECT 2,1,62.5
UNION ALL
SELECT 2,2,12.25
UNION ALL
SELECT 2,2,21.5
UNION ALL
SELECT 2,2,11.25
UNION ALL
SELECT 2,2,61.75
UNION ALL
SELECT 2,2,52.5
SELECT
EmpoyeeName ,
ProjectName,
TimeSpent ,
Bonus = CASE
WHEN TimeSpent > 200 THEN 20
WHEN TimeSpent > 150 THEN 15
WHEN TimeSpent > 100 THEN 5
ELSE 0
END
FROM
(
SELECT
ProjectID,
EmployeeID,
TimeSpent = SUM(TimeSpent)
FROM
#ProjectEmployeeHours
GROUP BY
ProjectID,EmployeeID
) ProjectTime
JOIN
#Employee e ON e.EmployeeID = ProjectTime.EmployeeID
JOIN
#Project p ON p.ProjectID = ProjectTime.ProjectID
Just a pure guess since you have failed to provide any useful information.
May 20, 2011 at 6:21 am
Why do you think I haven't replied... not going to waste 3 hours on a 5 minutes problem.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply