quote:

---

Originally posted by JeffS23

I need help on my sub-query below. Right now, I get the same date in all 18K rows in my dataset. I think it needs a little more tweaking.

[CODE]
SET NOCOUNT ON

DECLARE @startdate DATETIME,
@enddate DATETIME

set @startdate = ISNULL('01/01/2008','1/1/1900')
set @enddate = dateadd(day,1,ISNULL('12/31/2008','1/1/3000'))

SELECT
pv.TicketNumber ,
pp.Last ,
pp.First ,
dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [Patient Name],
pp.PatientID ,
pv.Visit ,
ISNULL(c.NAME , 'No Case') AS CaseName ,
(
SELECT
MAX(Visit)
FROM
PatientVisit pvv
JOIN PatientProfile ppp ON pvv.PatientProfileID = ppp.PatientProfileID
JOIN PatientVisitProcs pvp ON pvv.PatientVisitId = pvp.PatientVisitId
WHERE
pvv.PatientVisitId = pvp.PatientVisitID
pvv.PatientVisitId =pv.PatientVisitId
AND pvp.Code IN ( '59430' , '59515' , '59610' , '59612' , '59614' , '59618' , '59620' , '59622' )
) AS PostPartumDate

FROM PatientVisit pv
JOIN PatientProfile pp ON pv.PatientProfileID = pp.PatientProfileID
JOIN PatientVisitDiags pvd ON pv.PatientVisitID = pvd.PatientVisitID
LEFT JOIN Cases c ON c.PatientProfileID = pv.PatientProfileID
LEFT JOIN cusCasesPN cpn ON c.CasesID = cpn.CasesID
WHERE
LEFT(pvd.ICD9Code , 3) = 'V22'
AND pv.CasesID IS NULL
AND pv.Visit >= @startdate
AND pv.Visit < @enddate

ORDER BY
pp.Last ,
pp.First ,
pv.Visit
[/CODE]

---