CODEWITHSUNDEEP
sqljoinsum
Darrell
Darrell

Reputation: 49

How to join 2 tables so you can sum fields in both tables correctly

I am joining 2 tables and summing 3 fields, 2 from 1 table and 1 from the second. However the tables have multiple records and it is giving me incorrect totals (sums). I know what is wrong but do not know how to write the SQL to get the correct result.

Basically the totals are doubled because of 2 records for CCNum 001. So obviously the SQL is incorrect but what would be the correct SQL?

No idea what SQL would look like.

SELECT  AETCC.PayPeriodID, EI.ADP_ID, EI.LastName + ', ' 
 + EI.FirstName AS EmpName, AETCC.LocationNum 
 + '-' + AETCC.CostCenterNum AS CostString, 
 AETCC.LocationNum AS Location, AETCC.CostCenterNum AS CostCenter, 
 AllocationType, sum(AD.TotalHoursWorked) as TotalHoursWorked, 
 sum(AD.TotalPercentWorked) as TotalPercentWorked, 
 sum(AETCC.ExpenseAmount) AS ExpAmt
        FROM    dbo.AllocatedExpenseToCostCenter AS AETCC 
        INNER JOIN
            dbo.AllocationData AS AD 
                ON AETCC.ADP_ID = AD.ADP_ID 
                AND AETCC.CostCenterNum = AD.CostCenterNum 
                AND AETCC.LocationNum = AD.LocationNum 
                AND AETCC.PayPeriodID = AD.PayPeriodID 
        INNER JOIN
            dbo.EmployeeInformation AS EI 
                ON AETCC.ADP_ID = EI.ADP_ID
        WHERE        (AETCC.ExpenseAccount = '8000')
--      GROUP BY AETCC.PayPeriodID, EI.ADP_ID, EI.FirstName, EI.LastName,
--      AETCC.LocationNum, AETCC.CostCenterNum
        GROUP BY AETCC.PayPeriodID, AllocationType, AETCC.LocationNum,
        AETCC.CostCenterNum, EI.ADP_ID, EI.FirstName, EI.LastName

Data in first table:

PayPrd,LocNum,CCNum,ExpAcct,ExpenseAmount,ADP_ID,HoursWorked
1921,024,001,8000,175.2496,2063,0.63
1921,024,001,8000,1810.2617,2063,67.49
1921,024,013,8000,93.3417,2063,0.18

Data in second table:

PayPrd,LocNum,CCNum,Hours,%,ADP_ID
1921,024,001,7.50,0.0843,2063
1921,024,001,77.50,87.0787,2063
1921,024,013,4.00,0.0449,2063

Current Results:

PayPrd,ADP_ID,EmpName,CostString,LocNum,CCNum,Hours,%,ExpAmt
1921,2063,King; Tom,024-001,024,001,170.00,174,3971.0226
1921,2063,King; Tom,024-013,024,013,4.00,0.4,93.3417

Upvotes: 0

Views: 46

Answers (1)

Fred
Fred

Reputation: 2080

Sum from each table individually, then join the results.

SELECT  AETCC.PayPeriodID, EI.ADP_ID, EI.LastName + ', ' 
 + EI.FirstName AS EmpName, AETCC.LocationNum 
 + '-' + AETCC.CostCenterNum AS CostString, 
 AETCC.LocationNum AS Location, AETCC.CostCenterNum AS CostCenter, 
 AllocationType, AD.TotalHoursWorked as TotalHoursWorked, 
 AD.TotalPercentWorked as TotalPercentWorked, 
 AETCC.ExpenseAmount AS ExpAmt
      FROM (SELECT PayPeriodID, LocationNum, CostCenterNum, ADP_ID, ExpenseAccount,
              SUM(ExpenseAmount) as ExpenseAmount
            FROM dbo.AllocatedExpenseToCostCenter
            GROUP BY PayPeriodID, LocationNum, CostCenterNum, ADP_ID, ExpenseAccount
           ) AS AETCC 
        INNER JOIN
           (SELECT PayPeriodID, LocationNum, CostCenterNum, ADP_ID,
              SUM(TotalHoursWorked) as TotalHoursWorked,
              SUM(TotalPercentWorked) as TotalPercentWorked
            FROM dbo.AllocationData
            GROUP BY PayPeriodID, LocationNum, CostCenterNum, ADP_ID
           ) AS AD
                ON AETCC.ADP_ID = AD.ADP_ID 
                AND AETCC.CostCenterNum = AD.CostCenterNum 
                AND AETCC.LocationNum = AD.LocationNum 
                AND AETCC.PayPeriodID = AD.PayPeriodID 
        INNER JOIN
            dbo.EmployeeInformation AS EI 
                ON AETCC.ADP_ID = EI.ADP_ID
        WHERE        (AETCC.ExpenseAccount = '8000')

You could also move the WHERE clause inside the AETCC subquery.

Upvotes: 1

Related Questions