January 12, 2007 at 6:47 am
I have 3 tables:
Emp
Labor_Dept
Total
Emp has; EmpNum,EMPName,EMPDept,DeptNum,EMPType
Total has; EmpNum, DeptNum ,Total_1,Total_2,Total_3.
Labor_Dept has; EMPDept, DeptNum
I can get totals for the employee by using:
SELECT t.empname,
(CASE WHEN t.Salary < 40 THEN t.Salary ELSE 40 END) As Salary,
(CASE WHEN t.Hourly < 40 THEN t.Hourly ELSE 40 END) As Hourly,
(CASE WHEN t.Salary > 40 THEN t.Salary-40 ELSE 0 END) As OT_Salary,
(CASE WHEN t.Hourly > 40 THEN t.Hourly-40 ELSE 0 END) As OT_Hourly
FROM
(SELECT a.empname,
SUM((CASE a. EMPType WHEN 'Salary' THEN (b.TOTAL_1 + b.TOTAL_2 + b.TOTAL_3) ELSE 0 END)/60) AS Salary ,
SUM((CASE a. EMPType WHEN 'Hourly' THEN (b.TOTAL_1 + b.TOTAL_2 + b.TOTAL_3) ELSE 0 END)/60) AS Hourly
FROM emp a
Left Join Total b ON a.EMPNUM = b.EMPNUM
Left Join Labor_Dept c ON a. DeptNum = c. DeptNum
Group By a.empname) t
How can I just get the department?
I’m trying to use:
SELECT t.Deptnum,
(CASE WHEN t.Salary < 40 THEN t.Salary ELSE 40 END) As Salary,
(CASE WHEN t.Hourly < 40 THEN t.Hourly ELSE 40 END) As Hourly,
(CASE WHEN t.Salary > 40 THEN t.Salary-40 ELSE 0 END) As OT_Salary,
(CASE WHEN t.Hourly > 40 THEN t.Hourly-40 ELSE 0 END) As OT_Hourly
FROM
(SELECT c.Deptnum,
SUM((CASE a.EMPType WHEN 'Salary' THEN (b.TOTAL_1 + b.TOTAL_2 + b.TOTAL_3) ELSE 0 END)/60) AS Salary ,
SUM((CASE a.EMPType WHEN 'Hourly' THEN (b.TOTAL_1 + b.TOTAL_2 + b.TOTAL_3) ELSE 0 END)/60) AS Hourly
FROM emp a
left Join Total b ON a.empnum = b.empnum
left Join Labor_Dept c ON a.Deptnum = c.Deptnum
Group By c.Deptnum) t
My result values are in the thousands......
Thanks
CY
January 12, 2007 at 3:49 pm
You may get a bit more help if you could post some sample data as well as a sample result set. Full table DDL couldn't hurt either.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply