Why is this returning invalid records

  • 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

  • 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


  • 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

  • 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