Use a subquery to reject certain records - MS SQL Server 2008 -

i have query in want return results given has happend , reject others has happened , further yet reject others has not happened.

here current example output:

visit id | mrn | ord num | ord sts | ord desc 123456   | 123 | 987654  | active  | urine test 123456   | 123 | 987654  | in prog | urine test 123456   | 123 | 987654  | complete| urine test 123456   | 123 | 987321  | active  | insert foley 123456   | 123 | 987321  | in prog | insert foley 123456   | 123 | 987321  | complete| insert foley 124578   | 321 | 654321  | active  | urine test 124578   | 321 | 654321  | in prog | urine test 124578   | 321 | 654321  | complete| urine test end of report 

my desired output following:

visit id | mrn | ord num | ord sts | ord desc 123456   | 123 | 987654  | active  | urine test 123456   | 123 | 987654  | in prog | urine test 123456   | 123 | 987654  | complete| urine test 123456   | 123 | 987321  | active  | insert foley 123456   | 123 | 987321  | in prog | insert foley 123456   | 123 | 987321  | complete| insert foley end of report 

i want see visit ids have urine test if have either insert foley order or/and remove foley order. if come in , urine test, should not show on report.

here query:

-- foley catheter orders  -- variable declaration , initialization declare @sd datetime declare @ed datetime set @sd = '2013-06-01'; set @ed = '2013-06-30';  -- column selection select pv.ptno_num 'visit id' , pv.med_rec_no 'mrn' , pv.vst_start_dtime 'admit' , pv.vst_end_dtime 'disc' , pv.days_stay 'los' , pv.pt_type 'pt type' , pv.hosp_svc 'hosp svc' , so.ord_no 'order number' --, so.ent_dtime 'order entry time' --, datediff(hour,pv.vst_start_dtime,so.ent_dtime) 'adm entry hours' , case     when so.svc_desc = 'insert foley catheter' 'insert foley'     when so.svc_desc = 'insert indwelling urinary catheter gravity drainage' 'insert foley'     when so.svc_desc = 'remove indwelling urinary catheter' 'remove foley'     else so.svc_desc   end 'ord desc' , case     when osm.ord_sts = 'active' '1 - active'     when osm.ord_sts = 'in progress' '2 - in progress'     when osm.ord_sts = 'complete' '3 - complete'     when osm.ord_sts = 'cancel' '4 - cancel'     when osm.ord_sts = 'discontinue' '5 - discontinue'     when osm.ord_sts = 'suspend' '6 - suspend'   end 'order status' , sos.prcs_dtime 'order status time' , datediff(day,pv.vst_start_dtime,sos.prcs_dtime) 'adm ord sts in days'  -- db(s) used smsdss.bmh_plm_ptacct_v pv join smsmir.sr_ord on pv.ptno_num = so.episode_no join smsmir.sr_ord_sts_hist sos on so.ord_no = sos.ord_no join smsmir.ord_sts_modf_mstr osm on sos.hist_sts = osm.ord_sts_modf_cd  -- filter(s) pv.adm_date between @sd , @ed , (so.svc_desc 'insert foley catheter'     or so.svc_desc 'insert indwelling urinary catheter gravity drainage'     or so.svc_desc 'remove indwelling urinary catheter'     or so.svc_desc 'urin%'     ) -- trying kick out patients did not foley catheter order -- of type --and pv.ptno_num not in ( --    select so.ord_no  --   smsdss.bmh_plm_ptacct_v pv --  join smsmir.sr_ord --  on pv.ptno_num = so.episode_no --  join smsmir.sr_ord_sts_hist sos --  on so.ord_no = sos.ord_no --  join smsmir.ord_sts_modf_mstr osm --  on sos.hist_sts = osm.ord_sts_modf_cd  --  (so.svc_desc != 'insert foely catheter' --        or so.svc_desc != 'insert indwelling urinary catheter gravity drainage' --        or so.svc_desc != 'remove indwelling urinary catheter' --        ) --) -- kicks out discontinued orders , so.ord_no not in (     select so.ord_no      smsdss.bmh_plm_ptacct_v pv     join smsmir.sr_ord     on pv.ptno_num = so.episode_no     join smsmir.sr_ord_sts_hist sos     on so.ord_no = sos.ord_no     join smsmir.ord_sts_modf_mstr osm     on sos.hist_sts = osm.ord_sts_modf_cd      osm.ord_sts = 'discontinue'     , (so.svc_desc 'insert foley catheter'     or so.svc_desc 'insert indwelling urinary catheter gravity drainage'     or so.svc_desc 'remove indwelling urinary catheter'     or so.svc_desc 'urine%'     ) ) 

the code trying working rest of section entirely commented out. commented portion in query takes rather long time run meaning t>5 minutes no results coming back. without part query returns results in t<2 minutes

thanks in advance,

one way use window aggregate functions (e.g. max() on ...) determine whether visit id row group has rows particular ord desc values.

basically, pattern this:

select   ...   max(case when [ord desc] = 'urine test' 1 else 0 end)     on (partition [visit id]) hasurinetest,   max(case when [ord desc] in ('insert foley', 'remove foley') 1 else 0 end)     on (partition [visit id]) hasinsertremovefoley ... 

that give flag columns in every row, identical values per visit. use flags filter rows on. need put entire select subquery, though, , use derived table in order able filter on flag columns:

with flagged (   select     ...     max(case when [ord desc] = 'urine test' 1 else 0 end)       on (partition [visit id]) hasurinetest,     max(case when [ord desc] in ('insert foley', 'remove foley') 1 else 0 end)       on (partition [visit id]) hasinsertremovefoley    ...  -- joins   ...  -- , filters ) select ...  -- repeat columns except has* flags flagged hasurinetest = 1   , hasinsertremovefoley = 1 ; 

you may have noticed used output aliases rather underlying names or expressions in window functions. in actual implementation, [visit id] need replaced pv.ptno_num. [ord desc], introduce cross apply clause (sub)query , move [ord desc] calculation there instead of calculated now, this:

with flagged (   select      ...      x.[ord desc],  -- instead of case; case in cross apply      ...   ...  -- joins   cross apply (     select       case         when so.svc_desc = 'insert foley catheter' 'insert foley'         when so.svc_desc = 'insert indwelling urinary catheter gravity drainage' 'insert foley'         when so.svc_desc = 'remove indwelling urinary catheter' 'remove foley'         else so.svc_desc       end [ord desc]    ) x ) select ... ;