Need help to figure out why Sum function is adding incorrectly

Transact-SQL
1

Hi everyone,

I'm have a query that the SUM function is not adding correctly, and I'm not sure what the issue is. I'm using SQL Server 2017. The if I manually add the columns together, it comes out right. I did that with the SUM([TEST] + [TEST2]) lines. In the results it's correct. But when using the case statement the sum is way off see results in pic.

Here is the Query:
SELECT
[EMPLOYEE ID] AS 'EMPLOYEE ID',
[LAST NAME] AS 'LAST NAME',
[FIRST NAME] AS 'FIRST NAME',
[CALENDAR] AS 'CALENDAR',
[DEPARTMENT] AS 'DEPARTMENT',
[TYPE] AS 'TYPE',
MAX([DATE OF BIRTH]) AS 'DATE OF BIRTH',
MAX([DATE OF HIRE]) AS 'DATE OF HIRE',
MAX([ORIGINAL HIRE DATE]) AS 'ORIGINAL HIRE DATE',
MAX([2013 Fiscal Salary]) AS '2013 Fiscal Salary',
MAX([2014 Fiscal Salary]) AS '2014 Fiscal Salary',
MAX([2015 Fiscal Salary]) AS '2015 Fiscal Salary',
MAX([2016 Fiscal Salary]) AS '2016 Fiscal Salary',
MAX([2017 Fiscal Salary]) AS '2017 Fiscal Salary',
SUM([TEST] + [TEST2]) AS 'TEST'
FROM
(
SELECT
RTRIM(MAS.ID) AS 'EMPLOYEE ID',
RTRIM(MAS.FNAME) AS 'FIRST NAME',
RTRIM(MAS.LNAME) AS 'LAST NAME',
RTRIM(MAS.HDT) AS 'DATE OF HIRE',
RTRIM(MAS.BEG) AS 'ORIGINAL HIRE DATE',
RTRIM(MAS.BDT) AS 'DATE OF BIRTH',
RTRIM(MAS.CALENDAR) AS 'CALENDAR',
RTRIM(MAS.DEPARTMENT) AS 'DEPARTMENT',
RTRIM(PAY.POSITION) AS 'POSITION',
PAY.AS_OF_DATE AS 'DATE',
RTRIM(MAS.TYPE) AS 'TYPE',
P.COLUMN_2014_JAN_JUNE AS 'TEST',
P.COLUMN_2013_JULY_DECEMBER AS 'TEST2',
SUM(
case when PXD.pyx_yy = '2013' then (
P.column_2012_july_december + P.COLUMN_2013_JAN_JUNE
) ELSE 0 End
) AS '2013 Fiscal Salary',
SUM(
case when PXD.pyx_yy = '2014' then (
P.column_2013_july_december + P.COLUMN_2014_JAN_JUNE
) ELSE 0 End
) AS '2014 Fiscal Salary',
SUM(
case when PXD.pyx_yy = '2015' then (
P.column_2014_july_december + P.COLUMN_2015_JAN_JUNE
) ELSE 0 END
) AS '2015 Fiscal Salary',
SUM(
case when PXD.pyx_yy = '2016' then (
P.column_2015_july_december + P.COLUMN_2016_JAN_JUNE
) ELSE 0 END
) AS '2016 Fiscal Salary',
SUM(
case when PXD.pyx_yy = '2017' then (
P.column_2016_july_december + P.COLUMN_2017_JAN_JUNE
) ELSE 0 END
) AS '2017 Fiscal Salary'
FROM
HR_EMPMSTR MAS
left outer JOIN PYX_XTD_DTL PXD on mas.id = pxd.hr_pe_id
LEFT OUTER JOIN HR_EMPPAY PAY ON MAS.ID = PAY.ID
LEFT OUTER JOIN pyh_hst_dtl PHD ON MAS.ID = PHD.hr_pe_id
left outer join #pay P on mas.id = P.id
WHERE
PXD.PYX_YY = '2014'
AND PXD.PYX_NO = 6002
AND PXD.PYX_TTD > 0
AND PAY.PAYCLASS NOT IN (606, 605, 420, 410)
AND MAS.TYPE NOT IN ('BENE', 'RETI')
AND PHD.PYH_CK_DT = '2014-07-11'
AND MAS.ID = 'E02935'
GROUP BY
MAS.id,
MAS.fname,
MAS.lname,
MAS.HDT,
MAS.beg,
MAS.hdt,
MAS.bdt,
MAS.calendar,
MAS.department,
PAY.position,
PAY.as_of_date,
P.COLUMN_2014_JAN_JUNE,
P.COLUMN_2013_JULY_DECEMBER,
MAS.TYPE
) t
GROUP BY
[DEPARTMENT],
[LAST NAME],
[FIRST NAME],
[EMPLOYEE ID],
[CALENDAR],
[TYPE]
ORDER BY
[DEPARTMENT],
[LAST NAME]

Thank you

issue%20with%20sum%20function
issue with sum function.jpg1543×352 140 KB

2

Hard to know without seeing more of your data but could it be that

FROM HR_EMPMSTR MAS
left outer JOIN PYX_XTD_DTL PXD on mas.id = pxd.hr_pe_id
LEFT OUTER JOIN HR_EMPPAY PAY ON MAS.ID = PAY.ID
LEFT OUTER JOIN pyh_hst_dtl PHD ON MAS.ID = PHD.hr_pe_id

has incomplete predicates? MIght it return multiple rows where you are only expecting one?

That's what I would check first

3

Hi gbritton,

It only returns 1 row for each person. Also the temp table only has 1 row for each person.

Thanks.

4

You are summing the 2014 values for every fiscal year - regardless of what year it occurs:

This will return the values for every row...then you SUM the values that were returned from the inner query. The inner query is returning the FY2014 values 24 times.

1 Like
5

Hi jeffw8713,

Thank you for the insight. I will take a look at seeing how to correct it.