sql - Syntax for WHERE clause with AND/OR? -


i'm working on simple query pull list of patients @ clinic either have diagnosis or have had procedure done. diagnosis , procedure codes separate fields; patients diagnosis might not have had procedure done, , patients have had procedure might not have diagnosis, still need patients have had 1 or other. here's query i've got:

select patientid, patientname, dateofbirth visit visitstatus = 'complete'       , (diagnosiscode in ('abc','def','ghi') or procedurecode in ('123','456','789')) 

and yes, both code fields have varchar data type because codes use both letters , numbers.

this simplified version of where clause, since have upwards of 60 diagnosis codes , 30 procedure codes. wanted make sure syntax right. query taking absolutely forever run, don't know if that's because there's lot of data sift through or if there's issue syntax has gotten stuck in similar infinite loop. don't have syntax errors coming up. had diagnosis codes, where clause looked where .... , diagnosiscode in ('abc','def','ghi'). still took while run, did work. after added or part procedure codes, it's running , running , running.

does should running without problem? syntax correct?

you might little better taking union of 2 queries. can sql server produce better execution plan better hits indexes:

select patientid, patientname, dateofbirth visit visitstatus = 'complete'     , diagnosiscode in ('abc','def','ghi')  union  select patientid, patientname, dateofbirth visit visitstatus = 'complete'     , procedurecode in ('123','456','789') 

to gain further improvements, i'd these 2 indexes on table:

create nonclustered index myvisitindex1 on visit (visitstatus, procedurecode) include (patientid, patientname, dateofbirth); create nonclustered index myvisitindex2 on visit (visitstatus, diagnosiscode) include (patientid, patientname, dateofbirth); 

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -