subquery is returning incorrect value

• bkrehl

  Old Hand

  Points: 368

  More actions

  October 30, 2002 at 7:50 am

  #80257

  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

• Simon Sabin

  SSCrazy Eights

  Points: 8142

  More actions

  November 1, 2002 at 3:20 pm

  #440106

  I think your not realting your sub query to your main query. try this

  SELECT

  P1.FirstName,

  P1.LastName,

  P1.PersKey,

  EncCOunt.count_

  FROM Person P1

  JOIN (SELECT Encounter.OwnedBy_EmpPersKey AS PersKey,

  count(Encounter.EncounterNumber) count_

  FROM Encounter

  INNER JOIN EncounterType ON Encounter.EncounterTypeKey = EncounterType.EncounterTypeKey

  where EncounterType.EncounterType = 'KidCare Rebate'

  and Encounter.EncounterDate <= '10/29/2002'

  and Encounter.EncounterDate >= '8/1/2002'

  and Encounter.OwnedBy_EmpPersKey = 1) as EncCount ON EncCount.Perskey = P1.Perskey

  WHERE EXISTS (SELECT 1 FROM Employee

  INNER JOIN SystemUser ON Employee.EmpPersKey = SystemUser.EmpPersKey

  INNER JOIN Encounter ON SystemUser.EmpPersKey = Encounter.OwnedBy_EmpPersKey

  WHERE Encounter.EncounterDate <= '10/29/2002'

  and Encounter.EncounterDate >= '8/1/2002'

  and Employee.EmpPersKey = P1.Perskey)

  AND Person.PersKey = 1

  Simon Sabin

  Co-author of SQL Server 2000 XML Distilled

  http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

  
  Simon Sabin
  SQL Server MVP
  
  http://sqlblogcasts.com/blogs/simons