WHERE Condition help needed - 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)

WHERE Condition help needed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-02-12 : 11:16:14

Please note, the SQL is handled dynamically by the server, alot of whats in my Where clause will look odd to you.

In my results, I get back a patient that should not be in my result set. I input an end-date of 12/31/2008 and asked it to return all my Diabetic patients. This patient has 2 encounters in my demo database (DOS 02/26/2008 and DOS 01/21/2009). On the 02/26/2008 DOS, I added a 250.00 diagnosis code to the encounter. Per the UDS manual, the patient must "Have been seen in the clinic at least twice". This patient based on my through date was seen only 1 time, not twice.

I believe I need to input a condition in the WHERE clause on the #tmp table query, but I'm not quite sure how to set that up right. Any help is appreciated!

[CODE]
SET NOCOUNT ON

CREATE TABLE #Patients
(
PatientProfileID INT ,
PatientID VARCHAR(15) ,
MRN VARCHAR(15) ,
PatientName VARCHAR(100) ,
Birthdate DATETIME ,
Age VARCHAR(15) ,
Sex VARCHAR(1) ,
TicketNumber VARCHAR(15) ,
Visit DATETIME ,
PatientVisitID INT ,
ICD9Code VARCHAR(10) ,
Description VARCHAR(255) ,
RaceCode VARCHAR(50) ,
EthnicityCode VARCHAR(50) ,
RaceMID2 INT
)

DECLARE @AgeDate DATETIME
DECLARE @DXDate DATETIME

SET @AgeDate = '12/31/' + CONVERT(VARCHAR , YEAR('12/31/2008'))
SET @DXDate = '06/30/' + CONVERT(VARCHAR , YEAR('12/31/2008'))

INSERT INTO
#Patients
SELECT
pp.PatientProfileID ,
pp.PatientID ,
pp.MedicalRecordNumber AS MRN ,
RTRIM(RTRIM(ISNULL(pp.Last , '') + ' ' + ISNULL(pp.Suffix , '')) + ', ' + ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , '')) AS PatientName ,
pp.Birthdate ,
( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) AS Age ,
pp.Sex ,
pv.TicketNumber ,
pv.Visit ,
pv.PatientVisitID ,
pvd.ICD9Code ,
pvd.Description ,
CASE WHEN r.Code = 'H'
OR e.Code = 'H' THEN 'Hispanic/Latino'
WHEN r.Code = 'A' THEN 'Asian'
WHEN r.Code = 'NH' THEN 'Native Hawaiian'
WHEN r.Code = 'OPI' THEN 'Other Pacific Islander'
WHEN r.Code = 'B' THEN 'Black/African American'
WHEN r.Code = 'AI' THEN 'American Indian/Alaskan Native'
WHEN r.Code = 'W' THEN 'White'
WHEN r.Code = 'M' THEN 'More than one race'
WHEN r.Code = 'U' THEN 'Unreported'
ELSE 'Unreported'
END AS RaceCode ,
CASE WHEN r.Code = 'H'
OR e.Code = 'H' THEN 'Hispanic/Latino'
ELSE 'All Others'
END AS EthnicityCode ,
cri.RaceMID2
FROM
PatientProfile pp
LEFT JOIN PatientVisit pv ON pp.PatientProfileID = pv.PatientProfileId
LEFT JOIN PatientVisitDiags pvd ON pv.PatientVisitID = pvd.PatientVisitID
LEFT JOIN cusCRIInterview cri ON pp.patientprofileid = cri.patientprofileid
LEFT JOIN Medlists r ON pp.RaceMID = r.MedListsID
LEFT JOIN cusCRIMedLists e ON cri.RaceMID2 = e.MedListsID
WHERE
--Filter on facility
(
(
NULL IS NOT NULL
AND pv.FacilityID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND
--Filter on Company
(
(
NULL IS NOT NULL
AND pv.CompanyID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND ( DATEDIFF(day , pp.Birthdate , @AgeDate) / 365.25 ) > 17.99
AND (
(
2 = 1
AND pvd.ICD9Code LIKE ( '401.%' )
AND pv.Visit >= @DXDate
)
OR (
2 = 2
AND pv.Visit >= @DXDate
AND (
(pvd.ICD9Code LIKE ( '250.%' )
OR pvd.ICD9Code IN ( '357.2' , '362.0' , '366.41' )
OR pvd.ICD9Code LIKE ( '648.0%' ))
)
)
)
AND ( DATEPART(Year , pv.visit) ) > ( DATEPART(Year , ( '12/31/2008' )) ) - 1
GROUP BY
pp.PatientProfileID ,
pp.PatientId ,
pp.MedicalRecordNumber ,
pp.Birthdate ,
pp.Sex ,
pv.PatientVisitId ,
pvd.ICD9Code ,
pvd.Description ,
pv.TicketNumber ,
pv.Visit ,
pp.Last ,
pp.Suffix ,
pp.First ,
pp.Middle ,
r.code ,
e.code ,
cri.raceMID2
ORDER BY
pp.PatientProfileID

SELECT
p.PatientProfileID ,
COUNT(*) AS cnt ,
PatientID ,
MRN ,
PatientName ,
Birthdate ,
Age ,
Sex ,
RaceCode ,
EthnicityCode ,
RaceMID2
INTO
#tmp
FROM
#Patients p
LEFT JOIN PatientVisit pv ON p.PatientProfileID = pv.PatientProfileID
WHERE

GROUP BY
p.PatientProfileID ,
PatientID ,
MRN ,
PatientName ,
Birthdate ,
Sex ,
age ,
RaceCode ,
EthnicityCode ,
RaceMID2
HAVING
COUNT(*) > 1
ORDER BY
CASE WHEN '1' = 1 THEN MRN
WHEN '1' = 2 THEN PatientName
WHEN '1' = 3 THEN PatientID
ELSE NULL
END

--Select * from #Patients
SELECT * FROM #tmp

DROP TABLE #Patients
DROP TABLE #tmp
[/CODE]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-12 : 23:56:25

Include this condition also in u r WHERE clause of #Patients

AND COUNT(pp.PatientId) OVER (PARTITION BY pp.PatientId) >= 2

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 01:03:28

quote:
Originally posted by PeterNeo

Include this condition also in u r WHERE clause of #Patients
AND COUNT(pp.PatientId) OVER (PARTITION BY pp.PatientId) >= 2

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

you cant use windowed functions in WHERE clause

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources