| Author |
 Topic  |
|
hussainzim
Starting Member
21 Posts |
 Posted - 09/26/2007 : 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
Sweden
29138 Posts |
Posted - 09/26/2007 : 04:50:38
|
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" |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 09/26/2007 : 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
Time is always against us
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/26/2007 : 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 - 09/26/2007 : 05:13:00
|
| hey it says unable to parse query :( |
|
|
|
hussainzim
Starting Member
21 Posts |
Posted - 09/26/2007 : 05:30:15
|
| incorrect syntax near keyword "INNER" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/26/2007 : 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 - 09/26/2007 : 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
Sweden
29138 Posts |
Posted - 09/26/2007 : 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" |
Edited by - SwePeso on 09/26/2007 06:28:21 |
|
|
|
hussainzim
Starting Member
21 Posts |
Posted - 09/26/2007 : 06:34:33
|
| hey is it possible to write a trigger for this.. |
|
|
|
hussainzim
Starting Member
21 Posts |
Posted - 09/26/2007 : 06:36:31
|
| hey can i have ur msn or yahoo mail add..?? |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 09/26/2007 : 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'
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/26/2007 : 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
Sweden
29138 Posts |
Posted - 09/26/2007 : 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 - 09/26/2007 : 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
Sweden
29138 Posts |
Posted - 09/26/2007 : 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
Sweden
29138 Posts |
Posted - 09/26/2007 : 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" |
Edited by - SwePeso on 09/26/2007 07:31:33 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/26/2007 : 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 - 09/26/2007 : 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 - 09/26/2007 : 07:38:49
|
| ok but da question specifically says to write a trigger :(..so wht shall i do?? |
|
|
|
hussainzim
Starting Member
21 Posts |
Posted - 09/26/2007 : 07:42:20
|
| ok but da question specifically says to write a trigger :(..so wht shall i do?? |
|
|
|
Topic |
|
SQL to increment salary
