Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sum up information from separate tables

Status
Not open for further replies.

TomR100

Programmer
Joined
Aug 22, 2001
Messages
195
Location
US
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top