sql server - How to write this WHERE filter in my T-SQL Query that will cater for 2 conditions to be met? -

i using sql server 2014 , have table called resstaydate lists reservations made on property date. here extract of table:

 resaid    staydate      pkgplancode      rateamt  61200     2015-01-11      cogb              0.00  61200     2015-01-11      g1a810          152.00  61200     2015-01-11      g1a810          152.00  61200     2015-01-11      g1a810          152.00  63500     2015-02-04      r2ai            125.00  63500     2015-02-05      r2ai            125.00  73850     2015-05-10      cogb              0.00  73850     2015-05-10      g2450           169.00  68901     2015-05-15      cofr              0.00  68901     2015-05-15      cofr              0.00 

i want query output resaids (plus remaining columns of table) contain @ least 1 staydate pkgplancode "co..." , corresponding rateamt = 0. if staydates of particular resaid have pkgplancodes starting "co..." , corresponding rateamt = 0,then these resaids should excluded in output.

in other words, output should this:

resaid    staydate      pkgplancode      rateamt  61200     2015-01-11      cogb              0.00  61200     2015-01-11      g1a810          152.00  61200     2015-01-11      g1a810          152.00  61200     2015-01-11      g1a810          152.00  73850     2015-05-10      cogb              0.00  73850     2015-05-10      g2450           169.00 

the query should exclude resaid 63500 (since not contain pkgplancode starting "co..." , having rateamt = 0) , exclude resaid 68901 (as latter has of staydate pkgplancode starting "co..." , rateamt = 0)

my query stands follows @ (as stuck how handle condition in query):

select *  resstaydate  where....... 

we can use couple of ctes assess conditions required , use windowed aggregates determine if condition sometimes true across resaid values:

declare @t table (resaid int,staydate date,pkgplancode varchar(17),rateamt decimal(13,2)) insert @t(resaid,staydate,pkgplancode,rateamt) values  (61200,'20150111','cogb'  ,  0.00),  (61200,'20150111','g1a810',152.00),  (61200,'20150111','g1a810',152.00),  (61200,'20150111','g1a810',152.00),  (63500,'20150204','r2ai'  ,125.00),  (63500,'20150205','r2ai'  ,125.00),  (73850,'20150510','cogb'  ,  0.00),  (73850,'20150510','g2450' ,169.00),  (68901,'20150515','cofr'  ,  0.00),  (68901,'20150515','cofr'  ,  0.00)   ;with assessment (     select resaid,staydate,pkgplancode,rateamt,          case when pkgplancode 'co%' , rateamt=0.0 1 else 0 end cond     @t ), groups (     select *,         min(cond) on (partition resaid) mincond,         max(cond) on (partition resaid) maxcond             assessment ) select * groups mincond < maxcond 

result:

resaid      staydate   pkgplancode       rateamt    cond        mincond     maxcond ----------- ---------- ----------------- ---------- ----------- ----------- ----------- 61200       2015-01-11 cogb              0.00       1           0           1 61200       2015-01-11 g1a810            152.00     0           0           1 61200       2015-01-11 g1a810            152.00     0           0           1 61200       2015-01-11 g1a810            152.00     0           0           1 73850       2015-05-10 cogb              0.00       1           0           1 73850       2015-05-10 g2450             169.00     0           0           1