March 10, 2005 at 4:00 pm
I have a employee payroll table in SS2000:
For example:
EmpID PayCode PayAmt
123 1 10
123 2 15
456 1 9
789 3 6500
851 4 40000
333 5 1400
777 1 6.5
777 2 8
109 1 5.8
109 2 8.7
Each employee can have up to 5 records (5 PayCodes)I need to find out which employee has Paycode 1 and paycode 2 (not just a single paycode) and their payamt under paycode 2 is not 1.5 times of Paycode 1's payAmt.
For example, EmpID 777 has both paycode 1 and 2, but the PayCode 2's PayAmt=8 which should be 9.75 (1.5 times of 6.5 - PayAmt in Paycode 1).
So, I want to generate a query to list every single employee who has paycode 1 and has paycode 2 and compare their payAmt in Excel and show it to payroll department:
EmplID PayCode1 PayAmt PayCode2 PayAmt2
123 1 10 2 15
777 1 6.5 2 8
109 1 5.8 2 8.7
HOW CAN I DO THAT IN T-SQL? Please help. Thank you.
March 10, 2005 at 4:08 pm
A single SQL statement can reference the same table more than once as long as each reference assigns a unique name. For example:
from EmployeePayroll as EmployeePayroll_One
join EmployeePayroll as EmployeePayroll_TWO
A complete solution:
create table EmployeePayroll
( EmpID integer not null
, PayCode integer not null
, PayAmt numeric(12,2) not null
, constraint EmployeePayroll_PK primary key (EmpID , PayCode)
)
go
select EmployeePayroll_One.EmpId
, EmployeePayroll_One.PayCode
, EmployeePayroll_One.PayAmt
, EmployeePayroll_TWO.PayCode
, EmployeePayroll_TWO.PayAmt
from EmployeePayroll as EmployeePayroll_One
join EmployeePayroll as EmployeePayroll_TWO
on EmployeePayroll_One.EmpId = EmployeePayroll_two.EmpId
where EmployeePayroll_One.PayCode = 1
and EmployeePayroll_TWO.PayCode = 2
and EmployeePayroll_TWO.PayAmt
( 1.5 * EmployeePayroll_One.PayAmt)
SQL = Scarcely Qualifies as a Language
March 10, 2005 at 4:22 pm
Each employee can have up to 5 records (5 PayCodes)I need to ensure each employee : their payamt of PayCode 2 =1.5 * PayAmt of PayCode 1.
Example Table:
For example:
EmpID PayCode PayAmt
123 1 10
123 2 15
456 1 9
789 3 6500
851 4 40000
333 5 1400
777 1 6.5
777 2 8
109 1 5.8
109 2 8.7
For example, EmpID 777 as PayAmt $8 under PayCode2, , but it should be 9.75 (1.5 times of 6.5 - PayAmt in Paycode 1).
So, I want to have an UPDATE statement to Set every employee whose PayAmt of PayCode=2 equal to 1.5*PayAmt of PayCode=1.
HOW CAN I DO THAT IN T-SQL? Please help. Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply