January 12, 2009 at 7:54 am
SELECT e.EncounterID, SUM(ed.Balance) as TotalAmt
FROM ptEncounterDetails ed
INNER JOIN ptEncounterEncounterDetail eed
ON eed.EncounterDetailID = ed.EncounterDetailID
INNER JOIN ptEncounter e
On e.EncounterID = eed.EncounterID
WHERE TotalAmt >= 5.00 AND
ed.BucketID = 4 AND
ed.LatestRec = 1
GROUP BY e.EncounterID
Why would this code return records with TotalAmt = 0.00?
I am filtering in the WHERE asa TotalAmt >= 5.00
January 12, 2009 at 8:01 am
MrBaseball34 (1/12/2009)
SELECT e.EncounterID, SUM(ed.Balance) as TotalAmt
FROM ptEncounterDetails ed
INNER JOIN ptEncounterEncounterDetail eed
ON eed.EncounterDetailID = ed.EncounterDetailID
INNER JOIN ptEncounter e
On e.EncounterID = eed.EncounterID
WHERE TotalAmt >= 5.00 AND
ed.BucketID = 4 AND
ed.LatestRec = 1
GROUP BY e.EncounterID
Why would this code return records with TotalAmt = 0.00?
I am filtering in the WHERE asa TotalAmt >= 5.00
I doubt that this query will run unless you have the column "TotalAmt" in only one of the tables used in the query. If you have that column then probably the results are correct.
I think you want to filter the rows based on the aggregate, in that case you have to use HAVING clause instead of WHERE clause.
--Ramesh
January 12, 2009 at 8:04 am
MrBaseball34 (1/12/2009)
SELECT e.EncounterID, SUM(ed.Balance) as TotalAmt
FROM ptEncounterDetails ed
INNER JOIN ptEncounterEncounterDetail eed
ON eed.EncounterDetailID = ed.EncounterDetailID
INNER JOIN ptEncounter e
On e.EncounterID = eed.EncounterID
WHERE TotalAmt >= 5.00 AND
ed.BucketID = 4 AND
ed.LatestRec = 1
GROUP BY e.EncounterID
Why would this code return records with TotalAmt = 0.00?
I am filtering in the WHERE asa TotalAmt >= 5.00
I don't even think this query should be able to run, since you can't use column aliases in the WHERE clause. Is there another item called TotalAmt in one of the tables?
If you move the first part of the WHERE clause to the HAVING caluse, then the SQL will be this way:SELECT e.EncounterID, SUM(ed.Balance) as TotalAmt
FROM ptEncounterDetails ed
INNER JOIN ptEncounterEncounterDetail eed
ON eed.EncounterDetailID = ed.EncounterDetailID
INNER JOIN ptEncounter e
On e.EncounterID = eed.EncounterID
WHERE ed.BucketID = 4 AND
ed.LatestRec = 1
GROUP BY e.EncounterID
HAVING SUM(ed.Balance)>=5
Hope this helps. If my solution isn't sufficient, please post the table structure and sample data as described in the link below.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 12, 2009 at 8:20 am
Thanks, I forgot about the HAVING clause.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply