Using (And) OR operators correctly

Question

Using (And) OR operators correctly

Briceston

SSCrazy

Points: 2899
More actions
January 15, 2014 at 7:34 am

#282288

Hi guys,
I'm having a bit of trouble getting the accurate data for the below code, mainly with the And OR portion. What I expect is to return records where the fields are blank for the OR operators. I think my parentheses may be off.
Can you please provide some insight? Thanks in advance
SELECT
Distinct LTRIM(RTRIM([Serial_NMBR])) AS SerialNumber,
t2.MEDICAL_REC_NUM,
t3.PATIENT_NAME,
Convert(Varchar(10),t1.VISIT_DATE,101) as VisitDate,
t1.USER_DEF_CD7,
t1.ATTND_PHYS,
t2.PRIMARY_FINAN,
t2.REGISTER_AREA,
t1.CLINIC_ID,
t1.DIAG_CODE1,
t1.STATUS_CURRENT1 as ClosureStatus,
t1.DISCHARGE_DISP
Into #TempC
from OP_VISIT_HISTRY as t1
INNER JOIN
OUTPAT_REGISTER as t2
ON(t1.PATIENT_NUMBER = t2.PATIENT_NUMBER)
AND(t1.REGISTRATION_CD = t2.REGISTRATION_CD)
INNER JOIN
HR_PAT_MASTER as t3
on(t1.PATIENT_NUMBER = t3.PATIENT_NUMBER)
INNER JOIN
(
SELECT OP_SER_REG_LINK.TO_NUMBER AS SERIAL_NMBR,
RIGHT((LTRIM(RTRIM([from_number]))), 6) AS Registration_Code,
OP_SER_REG_LINK.SER_OR_ADM_IND
FROM OP_SER_REG_LINK
) AS [Serial Query]
ON (t1.REGISTRATION_CD = [Serial Query].Registration_Code)
WHERE ((([Serial Query].SER_OR_ADM_IND) = 'A'))
AND t1.DISCHARGE_DISP ! = 'VVX'
AND t1.VISIT_DATE = CONVERT(varchar,GETDATE(), 101)
OR (t1.Diag_Code1 = ' '
OR t1.ClosureStatus = ''
OR t1.DISCHARGE_DISP = '')

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

Difficult to answer your question but from what I understand, your derived table will return results if any of these are empty:

t1.Diag_Code1

t1.ClosureStatus

t1.DISCHARGE_DISP

Is that what you want?!

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I suspect (without any ability to test) that you want this

WHERE ((([Serial Query].SER_OR_ADM_IND) = 'A'))

AND t1.DISCHARGE_DISP ! = 'VVX'

AND t1.VISIT_DATE = CONVERT(varchar,GETDATE(), 101)

AND (t1.Diag_Code1 = '' OR t1.ClosureStatus = '' OR t1.DISCHARGE_DISP = '')

So the indicator must be A, the disp must not be VVX, the date must be today (and that's an inefficient way of doing that) and one or more of the three fields must be blank. Correct?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Briceston SSCrazy Points: 2899 More actions · Answer 3

Yes, that's correct. Sorry for not being more descriptive.

Briceston SSCrazy Points: 2899 More actions · Answer 4

GilaMonster (1/15/2014)
I suspect (without any ability to test) that you want this
WHERE ((([Serial Query].SER_OR_ADM_IND) = 'A'))
AND t1.DISCHARGE_DISP ! = 'VVX'
AND t1.VISIT_DATE = CONVERT(varchar,GETDATE(), 101)
AND (t1.Diag_Code1 = '' OR t1.ClosureStatus = '' OR t1.DISCHARGE_DISP = '')
So the indicator must be A, the disp must not be VVX, the date must be today (and that's an inefficient way of doing that) and one or more of the three fields must be blank. Correct?

So what's the correct and efficient way to utilize the And OR operators in this query?

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 5

So if I understand it correctly you want this:

WHERE

[Serial Query].SER_OR_ADM_IND) = 'A'

AND

t1.DISCHARGE_DISP ! = 'VVX'

AND

t1.VISIT_DATE = CONVERT(varchar,GETDATE(), 101) -- See Gail's comment

AND

(t1.Diag_Code1 = '' OR t1.ClosureStatus = '' OR t1.DISCHARGE_DISP = '')

or do you? In your OP you wrote this:

What I expect is to return records where the fields are blank for the OR operators.

What the where clause is saying is return a record as long as Diag_Code1 or ClosureStatus or Discharge_Disp is a blank string, not all three. What is the result that you are getting now that is incorrect?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Briceston (1/15/2014)
GilaMonster (1/15/2014)
I suspect (without any ability to test) that you want this
WHERE ((([Serial Query].SER_OR_ADM_IND) = 'A'))
AND t1.DISCHARGE_DISP ! = 'VVX'
AND t1.VISIT_DATE = CONVERT(varchar,GETDATE(), 101)
AND (t1.Diag_Code1 = '' OR t1.ClosureStatus = '' OR t1.DISCHARGE_DISP = '')
So the indicator must be A, the disp must not be VVX, the date must be today (and that's an inefficient way of doing that) and one or more of the three fields must be blank. Correct?
So what's the correct and efficient way to utilize the And OR operators in this query?

I don't know. What's correct?

If by 'correct' you're implying that my description is what you want, then the query portion I wrote is 'correct'. Otherwise, please explain what you want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass