subquery is returning incorrect value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

subquery is returning incorrect value

Author

Topic

bkrehl
Starting Member

2 Posts

Posted - 2002-10-30 : 09:56:17

I have a query from which I need to get the total number of encounters for a given person within a given date range. The subquery returns the correct number of encounters if I run it separately, but when it is include within the main query, I get the total encounters for that person for all dates. I can't figure out why it is not picking up my date constraints. Here is my query:

SELECT distinct Person.FirstName, Person.LastName, Person.PersKey,
(SELECT count(Encounter.EncounterNumber)
FROM SystemUser INNER JOIN
(Encounter INNER JOIN
EncounterType ON Encounter.EncounterTypeKey = EncounterType.EncounterTypeKey) ON
SystemUser.EmpPersKey = Encounter.OwnedBy_EmpPersKey
where EncounterType.EncounterType = 'KidCare Rebate'
and Encounter.EncounterDate <= '10/29/2002'
and Encounter.EncounterDate >= '8/1/2002'
and SystemUser.EmpPersKey = 1) as EncounterCount
FROM Person INNER JOIN Employee INNER JOIN SystemUser INNER JOIN
Encounter ON SystemUser.EmpPersKey = Encounter.OwnedBy_EmpPersKey
ON Employee.EmpPersKey = SystemUser.EmpPersKey
ON Person.PersKey = Employee.EmpPersKey
WHERE Encounter.EncounterDate <= '10/29/2002'
and Encounter.EncounterDate >= '8/1/2002'
and Person.PersKey = 1
group by Person.FirstName, Person.LastName, Person.PersKey, Encounter.EncounterDate

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-30 : 10:07:03

Same thing, right?


select
    p.FirstName,
    p.LastName,
    p.PersKey,
    count(*)
from
    dbo.Person p
    inner join dbo.Encounter e
        on p.PersKey = e.OwnedBy_EmpPersKey
    inner join dbo.EncounterType et
        on e.EncounterTypeKey = et.EncounterTypeKey
where
    et.EncounterType = 'KidCare Rebate' and
    e.EncounterDate between '8/1/2002' and '10/29/2002' and
    p.PersKey = 1
group by
    p.FirstName,
    p.LastName,
    p.PersKey

Jay White
{0}

bkrehl
Starting Member

2 Posts

Posted - 2002-10-30 : 10:25:07

It would be, except that I need to pull out several different types of encounters, so my actual query has other subqueries that read the same, except for a different type of encounter:

(SELECT COUNT(Encounter.EncounterNumber)
FROM SystemUser INNER JOIN
(Encounter INNER JOIN
EncounterType
ON Encounter.EncounterTypeKey = EncounterType.EncounterTypeKey)
ON SystemUser.EmpPersKey = Encounter.OwnedBy_EmpPersKey
WHERE EncounterType.EncounterType = 'KidCare Premium' and Encounter.EncounterDate <= @ToDate and dbo.Encounter.EncounterDate >= @FromDate and SystemUser.EmpPersKey = @PersKey) AS KidCarePremiumCount,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources