I am working on a project now to get year to date totals for the Finance department. I am trying to get the amounts from a quarterly wage table and a quarterly deduction table. When I execute the query the amounts are triple and quadruple the actual amounts. I am using a subquery within the query. The amounts are correct when I run the query against the quarterly wage or the quarterly deduction table separately.
Example:
SELECT EmployeeID,
SSN,
SUM(EmpFICA)AS FICA,
SUM(EmpMDC)as MDC,
SUM(EFICA)AS EFICA,
SUM(EMDC)AS EMDC,
SUM(Regular)AS Regular,
SUM(Salary)as Salary,
SUM(Overtime)AS Overtime,
SUM(Vacation)AS Vacation,
SUM(Sick)AS Sick,
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 EmpFICA,
case when QDeduct.DED_CODE = '1003' THEN
QDeduct.DED_AMT
else NULL
end AS EmpMDC,
case when QDeduct.DED_CODE = '1004' THEN
QDeduct.DED_AMT
else NULL
end AS EFICA,
case when QDeduct.DED_CODE = '1005' THEN
QDeduct.DED_AMT
else NULL
end AS EMDC,
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,
case when QWage.PAY_SUM_GRP = '31' THEN
QWage.WAGE_AMOUNT
else NULL
end AS Vacation,
case when QWage.PAY_SUM_GRP = '32' THEN
QWage.WAGE_AMOUNT
else NULL
end AS Sick
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
Example:
SELECT EmployeeID,
SSN,
SUM(EmpFICA)AS FICA,
SUM(EmpMDC)as MDC,
SUM(EFICA)AS EFICA,
SUM(EMDC)AS EMDC,
SUM(Regular)AS Regular,
SUM(Salary)as Salary,
SUM(Overtime)AS Overtime,
SUM(Vacation)AS Vacation,
SUM(Sick)AS Sick,
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 EmpFICA,
case when QDeduct.DED_CODE = '1003' THEN
QDeduct.DED_AMT
else NULL
end AS EmpMDC,
case when QDeduct.DED_CODE = '1004' THEN
QDeduct.DED_AMT
else NULL
end AS EFICA,
case when QDeduct.DED_CODE = '1005' THEN
QDeduct.DED_AMT
else NULL
end AS EMDC,
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,
case when QWage.PAY_SUM_GRP = '31' THEN
QWage.WAGE_AMOUNT
else NULL
end AS Vacation,
case when QWage.PAY_SUM_GRP = '32' THEN
QWage.WAGE_AMOUNT
else NULL
end AS Sick
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