March 10, 2005 at 3:15 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 11, 2005 at 1:38 am
This isn't quite what you asked for, it's a bit more specific in regard it only looks for empId's that have codes 1 and 2 and only when the amt on code 2 isn't 1.5 times the value of code 1.
So, the query won't show any rows that has the 'correct' relationships between codes and amounts.
select p.empId,
p.payCode,
p.payAmt
from (
select p1.empId
from payroll p1
join payroll p2
on p1.empId = p2.empId
and p1.payCode = 1
and p2.payCode = 2
and (p2.payAmt <> (p1.payAmt * 1.5))
) x
join payroll p
on x.empId = p.empId
order by p.empId,
p.payCode
empId payCode payAmt
----------- ------- ------------
777 1 6.5
777 2 8.0
(2 row(s) affected)
To pivot this in Transact SQL, you would need a fair amount of convoluted code stacked on top of this query, but why not pivot in Excel, if that's where the data is intended to go anyway?
/Kenneth
March 11, 2005 at 7:21 am
SELECT t1.EmpID, t1.PayCode, t1.PayAmt, t2.PayCode, t2.PayAmt
FROM #table t1
INNER JOIN #table t2
ON t2.EmpID = t1.EmpID
AND t2.PayCode = 2
AND t2.PayAmt <> (t1.PayAmt * 1.5)
WHERE t1.PayCode = 1
Far away is close at hand in the images of elsewhere.
Anon.
March 11, 2005 at 8:01 am
To easily solve all types of pivoting problems with minimal coding check out the RAC utility for S2k.As easy as Access crosstab query but much more powerful with many features/options.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply