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 eSET e.Salary = COALESCE(e.Salary, 0) * 1.1INNER 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.EmployeeIDWHERE 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 eSET e.Salary = COALESCE(e.Salary, 0) * 100INNER 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.EmployeeIDWHERE 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 eSET e.Salary = COALESCE(e.Salary, 0) * 1.1FROM Employee AS eINNER 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.EmployeeIDWHERE 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 dataDECLARE @Employee TABLE (EmployeeID INT, Name SYSNAME, Salary MONEY, Type SYSNAME)INSERT @EmployeeSELECT 1, 'Peso', 120, 'Technician' UNION ALLSELECT 2, 'Hussainzim', 1000000, 'Traffic controller'DECLARE @TestEmployee TABLE (TestNo INT, EmployeeID INT, Hrs SMALLMONEY)INSERT @TestEmployeeSELECT 1, 1, 5 UNION ALLSELECT 1, 2, 45 UNION ALLSELECT 2, 3, 1DECLARE @Test TABLE (TestNo INT, TestDate DATETIME, Result TINYINT)INSERT @TestSELECT 1, '20070925 15:00:00', 1 UNION ALLSELECT 1, '20070925 13:00:00', 1 UNION ALLSELECT 1, '20070925 14:00:00', 1 UNION ALLSELECT 2, '20070926 11:00:00', 1 UNION ALLSELECT 2, '20070925 11:00:00', 1 UNION ALLSELECT 2, '20070925 12:00:00', 1 UNION ALLSELECT 3, '20070925 15:00:00', 0-- Show preconditionSELECT *FROM @Employee-- Do the magic as posted 09/26/2007 : 04:50:38 and 09/26/2007 : 05:34:26UPDATE eSET e.Salary = COALESCE(e.Salary, 0) * 1.1FROM @Employee AS eINNER 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.EmployeeIDWHERE e.Type = 'Technician'-- Show postconditionSELECT *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]MadhivananFailing 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 TableT01 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 AMT05 3 5/7/2007 12:00:00 AMT06 3 5/8/2007 12:00:00 AMT07 3 5/9/2007 12:00:00 AM TestEmployee TableT01 EMP001 2T01 EMP002 4T02 EMP001 3T02 EMP002 3T02 EMP006 2T02 EMP008 3T02 EMP010 4T03 EMP001 2T03 EMP009 4T04 EMP001 1T04 EMP002 4T04 EMP008 3T04 EMP010 2T05 EMP001 3so 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 TableTestNo,Result,DateT01 5 5/5/2007 12:00:00 AMT02 5 5/6/2007 12:00:00 AMT03 3 5/7/2007 12:00:00 AMT04 3 5/7/2007 12:00:00 AMT05 3 5/7/2007 12:00:00 AMT06 3 5/8/2007 12:00:00 AMT07 3 5/9/2007 12:00:00 AMTestEmployee TableTestNo,EmployeeId,HrsT01 EMP001 2T01 EMP002 4T02 EMP001 3T02 EMP002 3T02 EMP006 2T02 EMP008 3T02 EMP010 4T03 EMP001 2T03 EMP009 4T04 EMP001 1T04 EMP002 4T04 EMP008 3T04 EMP010 2T05 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?? |
|
|
Next Page
|