Information from two tables not calculating correctly

[TomR100]

I am trying to extract data from two different tables and getting the information into one temporary table. There is a problem though. I am trying to get a yearly total for each variable in the temporary and it does not calculate the amount correctly. The amounts are doubled and tripled in some cases.

Thanks

 

[rac2]

Sounds like an incorrect join.

Show us more detail on pertinent data and key columns in the tables.

 

[TomR100]

Here is an example of my code.

SELECT EmployeeID,
SSN,
SUM(EmpFICA)AS EmployeeFICA,
SUM(EmpMDC)as EmployeeMDC,
SUM(EFICA)AS EmployerFICA,
SUM(Regular)AS Regular,
SUM(Salary)as Salary,
SUM(Overtime)AS Overtime,

FROM (SELECT Emp.EMPLOYEE AS EmployeeID,
SUBSTRING(Emp.FICA_NBR,1,3)+
SUBSTRING(Emp.FICA_NBR,5,2) +
SUBSTRING(Emp.FICA_NBR,8,4) as SSN,
case when QDeduct.DED_CODE = '1002'
THEN QDeduct.DED_AMT
else NULL
end AS EmployeeFICA,
case when QDeduct.DED_CODE = '1003'
THEN QDeduct.DED_AMT
else NULL
end AS EmployeeMDC,
case when QDeduct.DED_CODE = '1004'
THEN QDeduct.DED_AMT
else NULL
end AS EmployerFICA,
case when QWage.PAY_SUM_GRP = '01'
THEN QWage.WAGE_AMOUNT
else NULL
end as Regular,
case when QWage.PAY_SUM_GRP = '30'
THEN QWage.WAGE_AMOUNT
else NULL
end AS Salary,
case when QWage.PAY_SUM_GRP = '02'
THEN QWage.WAGE_AMOUNT
else NULL
end AS Overtime
FROM QUARTDED AS QDeduct left JOIN EMPLOYEE AS Emp ON (QDeduct.EMPLOYEE = Emp.EMPLOYEE)
left JOIN QUARTWAGE AS QWage ON (QWage.EMPLOYEE = Emp.EMPLOYEE)
WHERE QDeduct.PAYROLL_YEAR = '2001'
AND QDeduct.QUARTER IN ('1','2','3')
AND QWage.PAYROLL_YEAR = '2001'
AND QWage.QUARTER IN ('1','2','3')
AND Emp.UNION_CODE = ''
AND Emp.EMP_STATUS IN
('21','31','32','33','34','35','36','37','38','39')
AND Emp.EMPLOYEE = '19773')
AS TheData
GROUP BY EmployeeID, SSN

 

[jhall156]

Do you want the figures for each employee regardless of how much information you have in the deduction and wage tables?
If so, then I think you want the Employee table on the left for both joins.

FROM Employee LEFT JOIN wage on Employee.EMPLOYEE = Deduct.EMPLOYEE LEFT JOIN Wage ON Employee.EMPLOYEE. = Wage.EMPLOYEE
GROUP BY EmplyeeID I started out with nothing, and I still have most of it.