Subquery Help Again :( - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Subquery Help Again :(

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-24 : 20:04:37

I am getting results back from my Subquery, however they are not the right facility on the Patient Visit. Thoughts?

My full query:

DECLARE @pPatientVisitID INT
SELECT @pPatientVisitID = pv.PatientVisitID
FROM PatientVisit pv

SELECT cIPV.ImmunCode,
cIPV.ImmunDescription,
cIPV.Category,
pp.Last,
pp.First,
ISNULL(pp.Middle,'') AS Middle,
cIPV.ImmunDte,
cIPV.ListOrder,
cIPV.Last + ', ' + cIPV.First + case when cIPV.middle is null then + '' else ' ' + cIPV.middle end as Name,
dbo.cusCalcAgeYearsMonths(AgeYears,AgeMonths) AS AgeAtImmun,
pp.PatientID AS MedicalRecordNumber,
(SELECT fac.ListName
FROM cusvImmunPatVisits cIPV
INNER JOIN PatientVisit pv ON cIPV.PatientVisitID = pv.PatientVisitID
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
WHERE cIPV.PatientvisitId = @pPatientVisitID
) AS Facility

FROM cusvImmunPatVisits cIPV
INNER JOIN PatientProfile pp ON cIPV.PatientProfileID = pp.PatientProfileID

WHERE cIPV.ImmunDte >= ISNULL(NULL,'1/1/1900') AND cIPV.ImmunDte < dateadd(day,1,ISNULL(NULL,'1/1/3000'))
AND --Filter on patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-03-24 : 20:05:25

Subquery that needs looking at:

(SELECT fac.ListName
FROM cusvImmunPatVisits cIPV
INNER JOIN PatientVisit pv ON cIPV.PatientVisitID = pv.PatientVisitID
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
WHERE cIPV.PatientvisitId = @pPatientVisitID
) AS Facility

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources