Summing up data in SQL

[Deam]

I am selecting data from 3 different tables:

tEmpPersonID,FullName)
tBenefitHistPersonID,BenefitID,BenefitStartDate,BenefitEndDate,PlanCode)
tContributionHistPersonID,BenefitID,ContributionStartDate,ContributionEndDate,EmployerAmount)

I need to SUM the employeramounts(from table:tContributionHist)for each employee..this statement below is returning multiple rows...any suggestions will be greatly appreciated...


Declare @StartDate datetime,@EndDate datetime

Select @StartDate = '1/1/2006'
Select @EndDate = '1/1/2007'


SELECT distinct p.PersonID,p.FullName, SUM(EmployerAmount) as TotalEmployerAmount
FROM tEmp p
LEFT JOIN tBenefitHist bh
on bh.PersonID = p.PersonID
AND bh.BenefitStartDate BETWEEN @StartDate AND ISNULL(@EndDate, '99990101')
AND bh.BenefitStartDate = ((SELECT MAX(a2.BenefitStartDate)FROM tBenefitHist a2 WHERE a2.PersonID = bh.PersonID))
LEFT JOIN tContributionHist pbch
ON bh.PersonID = pbch.PersonID
AND bh.BenefitID = pbch.BenefitID
AND ContributionStartDate =(SELECT MAX(b2.ContributionStartDate)FROM tContributionHist b2
WHERE b2.PersonbenefitID = bh.PersonbenefitID)
WHERE AND pbch.ContributionStartDate =
(SELECT MAX(b2.ContributionStartDate)FROM tContributionHist b2 WHERE b2.PbenefitID = bh.benefitID )
Group by p.PersonID,p.FullName,pbch.PersonID,bh.PersonID,bh.BenefitID,pbch.BenefitID,ContributionStartDate
order by p.fullname

 

[GeertVerhoeven]

Hi,

I don't really know the content of the tables but what I see is that you've added the ContributionStartDate field to the group by clause.

Try your query without this field in the group by and see what it does.

Greetz,

Geert


Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[Deam]

That did not do the trick...any other suggestions

 

[KeertanaCR]

try with

group by p.personId,P.fullname