SUM in SELECT problem

[DavidC]

I have the following SQL that is not getting the correct value in TotalPoints
which is coming from a SUM of records for a PeopleLinkID. Can anyone see
what I am missing or have a better solution? Thanks.

SELECT H.PeopleLinkID,
(SELECT SUM(dbo.ActivityCodes.Points)
FROM dbo.ActivityHistory INNER JOIN
dbo.ActivityCodes ON dbo.ActivityHistory.ActivityCode =
dbo.ActivityCodes.ActivityCode INNER JOIN
dbo.PeopleLink ON dbo.ActivityHistory.PeopleLinkID =
dbo.PeopleLink.PeopleLinkID
WHERE (dbo.PeopleLink.PeopleEnd IS NULL)
AND (dbo.ActivityHistory.ActivityDate BETWEEN @StartDate AND @EndDate)
AND (dbo.ActivityHistory.PeopleLinkID = dbo.PeopleLink.PeopleLinkID)
AND (dbo.ActivityCodes.Points IS NOT NULL)
GROUP BY dbo.ActivityHistory.PeopleLinkID) AS TotalPoints,
H.ActivityDate,
C.ActivityName,
H.ActivityNotes,
P.LastName + N', ' + P.FirstName AS Worker
FROM dbo.ActivityHistory H INNER JOIN
dbo.ActivityCodes C ON H.ActivityCode = C.ActivityCode INNER JOIN
dbo.PeopleLink L ON H.PeopleLinkID = L.PeopleLinkID INNER JOIN
dbo.People P ON L.PersonID = P.PersonID
WHERE (C.Points IS NOT NULL)
AND (H.ActivityDate BETWEEN @StartDate AND @EndDate)

--
David

[Erland Sommarskog]

DavidC (dlc...@lifetimeinc.com) writes:
> I have the following SQL that is not getting the correct value in
> TotalPoints which is coming from a SUM of records for a PeopleLinkID.
> Can anyone see what I am missing or have a better solution? Thanks.

Of course not. I'm sorry if I'm obnoxious, but if you think that
just posting a query, without no description of the tables, the
business rules, table definitions etc, there is no idea anywhere here
can say why you don't get the expected result.

What I can say though is that there is one thing which is funny
about your subquery: it is not correlated to the rest of the
query, so you get the same value for all rows.

This line in the WHERE clause of the subquery looks particularly fishy:

AND (dbo.ActivityHistory.PeopleLinkID = dbo.PeopleLink.PeopleLinkID)

Because it duplicates one of the ON clauses. Maybe you meant:

AND (dbo.ActivityHistory.PeopleLinkID = L.PeopleLinkID)

Which would make the query correlated.

But I like to stress that this is just a guessing game I play. I
have absolutely no idea if this the right thing or not.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[DavidC]

You were absolutely right! I hesitated to post the table definitions as
there were several and their relationships varied. I realize it is hard to
figure out as I run into it also when called out to another client with
someone elses design. I will try to do better next time. Thank you.
--
David

"Erland Sommarskog" wrote:

> .
>