SQL to increment salary - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL to increment salary

Author

Topic

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 04:07:24

i want to write a SQL statement to increment the salary by 10% for technicians who have done three tests on a particular date.

there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.

Employee (EmployeeID,Name,Salary,Tpye)

TestEmployee(TestNo,EmployeeID,Hrs)

Test(TestNo,TestDate,Result)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 04:50:38

[code]UPDATE e
SET e.Salary = COALESCE(e.Salary, 0) * 1.1
INNER JOIN (
SELECT DISTINCT te.EmployeeID
FROM TestEmployee AS te
INNER JOIN (
SELECT DISTINCT TestNo
FROM Test
GROUP BY TestNo,
DATEDIFF(DAY, 0, TestDate)
HAVING COUNT(*) >= 3
) AS t ON t.TestNo = te.TestNo
) AS q ON q.EmployeeID = e.EmployeeID
WHERE e.Type = 'Technician'[/code]

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-26 : 04:57:04

Just couldn't resist this


UPDATE		e
SET		e.Salary = COALESCE(e.Salary, 0) * 100
INNER JOIN	(
			SELECT DISTINCT	te.EmployeeID
			FROM		TestEmployee AS te
			INNER JOIN	(
						SELECT DISTINCT	TestNo
						FROM		Test
						GROUP BY	TestNo,
								DATEDIFF(DAY, 0, TestDate)
						HAVING		COUNT(*) >= 3
					) AS t ON t.TestNo = te.TestNo
		) AS q ON q.EmployeeID = e.EmployeeID
WHERE		e.Type = 'DBA'

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 05:02:03

quote:
Originally posted by khtan

WHERE e.Type = 'DBA'

WHERE		e.Type = 'khtan'

E 12°55'05.25"
N 56°04'39.16"

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 05:13:00

hey it says unable to parse query :(

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 05:30:15

incorrect syntax near keyword "INNER"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 05:34:26

Sorry...

UPDATE		e
SET		e.Salary = COALESCE(e.Salary, 0) * 1.1
FROM		Employee AS e
INNER JOIN	(
			SELECT DISTINCT	te.EmployeeID
			FROM		TestEmployee AS te
			INNER JOIN	(
						SELECT DISTINCT	TestNo
						FROM		Test
						GROUP BY	TestNo,
								DATEDIFF(DAY, 0, TestDate)
						HAVING		COUNT(*) >= 3
					) AS t ON t.TestNo = te.TestNo
		) AS q ON q.EmployeeID = e.EmployeeID
WHERE		e.Type = 'Technician'

E 12°55'05.25"
N 56°04'39.16"

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 06:06:54

it says invalid object name 'e'.Column or expression 'Salary' could not be updated. im using SQL SERVER 2005.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 06:27:22

It works for me. I think you are not copying and pasting the complete solution!

-- Prepare sample data
DECLARE	@Employee TABLE (EmployeeID INT, Name SYSNAME, Salary MONEY, Type SYSNAME)

INSERT	@Employee
SELECT	1, 'Peso', 120, 'Technician' UNION ALL
SELECT	2, 'Hussainzim', 1000000, 'Traffic controller'

DECLARE	@TestEmployee TABLE (TestNo INT, EmployeeID INT, Hrs SMALLMONEY)

INSERT	@TestEmployee
SELECT	1, 1, 5 UNION ALL
SELECT	1, 2, 45 UNION ALL
SELECT	2, 3, 1

DECLARE	@Test TABLE (TestNo INT, TestDate DATETIME, Result TINYINT)

INSERT	@Test
SELECT	1, '20070925 15:00:00', 1 UNION ALL
SELECT	1, '20070925 13:00:00', 1 UNION ALL
SELECT	1, '20070925 14:00:00', 1 UNION ALL
SELECT	2, '20070926 11:00:00', 1 UNION ALL
SELECT	2, '20070925 11:00:00', 1 UNION ALL
SELECT	2, '20070925 12:00:00', 1 UNION ALL
SELECT	3, '20070925 15:00:00', 0

-- Show precondition
SELECT	*
FROM	@Employee

-- Do the magic as posted 09/26/2007 : 04:50:38 and 09/26/2007 : 05:34:26
UPDATE		e
SET		e.Salary = COALESCE(e.Salary, 0) * 1.1
FROM		@Employee AS e
INNER JOIN	(
			SELECT DISTINCT	te.EmployeeID
			FROM		@TestEmployee AS te
			INNER JOIN	(
						SELECT DISTINCT	TestNo
						FROM		@Test
						GROUP BY	TestNo,
								DATEDIFF(DAY, 0, TestDate)
						HAVING		COUNT(*) >= 3
					) AS t ON t.TestNo = te.TestNo
		) AS q ON q.EmployeeID = e.EmployeeID
WHERE		e.Type = 'Technician'

-- Show postcondition
SELECT	*
FROM	@Employee

E 12°55'05.25"
N 56°04'39.16"

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 06:34:33

hey is it possible to write a trigger for this..

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 06:36:31

hey can i have ur msn or yahoo mail add..??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 06:46:32

quote:
Originally posted by Peso

quote:
Originally posted by khtan

WHERE e.Type = 'DBA'
WHERE e.Type = 'khtan'

E 12°55'05.25"
N 56°04'39.16"

WHERE		e.Name= 'khtan'

[/code]

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 07:02:41

quote:
Originally posted by hussainzim

hey is it possible to write a trigger for this..

Yes.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 07:02:57

quote:
Originally posted by hussainzim

hey can i have ur msn or yahoo mail add..??

No.

E 12°55'05.25"
N 56°04'39.16"

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 07:23:57

hey i think i cudnt explain the problem in detail for u. srry for that. "TestNo" is the primary key in Test Table.thus it cant repeat. so when checking for 3 test by a technician for a day it has to be checked TestEmployee table.The solution has to be written in trigger.

Example -:

Test Table
T01 5 5/5/2007 12:00:00 AM
T02 5 5/6/2007 12:00:00 AM
T03 3 5/7/2007 12:00:00 AM
T04 3 5/7/2007 12:00:00 AM
T05 3 5/7/2007 12:00:00 AM
T06 3 5/8/2007 12:00:00 AM
T07 3 5/9/2007 12:00:00 AM

TestEmployee Table

T01 EMP001 2
T01 EMP002 4
T02 EMP001 3
T02 EMP002 3
T02 EMP006 2
T02 EMP008 3
T02 EMP010 4
T03 EMP001 2
T03 EMP009 4
T04 EMP001 1
T04 EMP002 4
T04 EMP008 3
T04 EMP010 2
T05 EMP001 3

so if u execute the quesrry the answer should be,
only EMP001 has to get an increment. since T3,T4, and T5 tests are done on the same day and EMP001 is involved in all three tests.Hope this makes the problem clear.thank you in advance...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 07:29:24

So esentially this is a completely new problem?
Because you can't tell what you want? And can't tell what you have?
Fo example, you don't even have column names to the sample data above!

And you expect us to know how to write your trigger anyway?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 07:30:27

A trigger is a VERY VERY VERY VERY bad idea in this case!
If a NEW test is made for an employee that already have three test for a certain day, he/she ALWAYS get a raise, every time the trigger is executed!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 07:34:52

And you should mentioned that you use SQL Server 2005, as you have done on other sites.

E 12°55'05.25"
N 56°04'39.16"

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 07:35:32

oh srry :(

Test Table

TestNo,Result,Date
T01 5 5/5/2007 12:00:00 AM
T02 5 5/6/2007 12:00:00 AM
T03 3 5/7/2007 12:00:00 AM
T04 3 5/7/2007 12:00:00 AM
T05 3 5/7/2007 12:00:00 AM
T06 3 5/8/2007 12:00:00 AM
T07 3 5/9/2007 12:00:00 AM

TestEmployee Table

TestNo,EmployeeId,Hrs

T01 EMP001 2
T01 EMP002 4
T02 EMP001 3
T02 EMP002 3
T02 EMP006 2
T02 EMP008 3
T02 EMP010 4
T03 EMP001 2
T03 EMP009 4
T04 EMP001 1
T04 EMP002 4
T04 EMP008 3
T04 EMP010 2
T05 EMP001 3

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 07:38:49

ok but da question specifically says to write a trigger :(..so wht shall i do??

hussainzim
Starting Member

21 Posts

Posted - 2007-09-26 : 07:42:20

ok but da question specifically says to write a trigger :(..so wht shall i do??

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources