SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL to increment salary
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  04:07:24  Show Profile  Reply with Quote
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

Sweden
30116 Posts

Posted - 09/26/2007 :  04:50:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 09/26/2007 :  04:57:04  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/26/2007 :  05:02:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by khtan

WHERE		e.Type = 'DBA'

WHERE		e.Type = 'khtan'



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  05:13:00  Show Profile  Reply with Quote
hey it says unable to parse query :(
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  05:30:15  Show Profile  Reply with Quote
incorrect syntax near keyword "INNER"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/26/2007 :  05:34:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  06:06:54  Show Profile  Reply with Quote
it says invalid object name 'e'.Column or expression 'Salary' could not be updated. im using SQL SERVER 2005.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/26/2007 :  06:27:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/26/2007 06:28:21
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  06:34:33  Show Profile  Reply with Quote
hey is it possible to write a trigger for this..
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  06:36:31  Show Profile  Reply with Quote
hey can i have ur msn or yahoo mail add..??
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 09/26/2007 :  06:46:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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'

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/26/2007 :  07:02:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/26/2007 :  07:02:57  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  07:23:57  Show Profile  Reply with Quote
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...
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/26/2007 :  07:29:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/26/2007 :  07:30:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 09/26/2007 07:31:33
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 09/26/2007 :  07:34:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  07:35:32  Show Profile  Reply with Quote
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
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  07:38:49  Show Profile  Reply with Quote
ok but da question specifically says to write a trigger :(..so wht shall i do??
Go to Top of Page

hussainzim
Starting Member

21 Posts

Posted - 09/26/2007 :  07:42:20  Show Profile  Reply with Quote
ok but da question specifically says to write a trigger :(..so wht shall i do??
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000