sql server 2008 - How to exclude rows from a SQL query with an "IF"-like condition -

sql server 2008 - How to exclude rows from a SQL query with an "IF"-like condition -

i have feeling reply staring @ me right in face...i can't see it. i've ran problem many times, can't seem wrap head around solid reply it. need exclude rows sql query simple condition. here code:

if object_id('tempdb..#nextevent') not null drop table #nextevent create table #nextevent( casedisplaynumber varchar(20) , scheduledday datetime , eventtypeid int , eventtype varchar(50) , eventresult varchar(50) , mattertypeid int , rownum int ) insert #nextevent select cc.casedisplaynumber ,sd.scheduledday ,se.eventtypeid eventtypeid ,etc.eventtype eventtype ,erc.eventresult ,mtc.mattertypeid ,row_number() over(partition cc.casedisplaynumber order sd.scheduledday desc) courtcase cc left bring together calendar.caseassociateevent ccae on ccae.courtcaseid = cc.courtcaseid left bring together calendar.caseeventheader ceh on ceh.caseeventheaderid = ccae.caseeventheaderid left bring together calendar.scheduledday sd on sd.caseeventheaderid = ceh.caseeventheaderid left bring together calendar.scheduledevent se on se.caseeventheaderid = ceh.caseeventheaderid left bring together calendar.scheduledresult sr on sr.scheduledeventid = se.scheduledeventid left bring together calendar.eventresultcodes erc on erc.eventresultid = sr.eventresultid left bring together calendar.mattertypecodes mtc on mtc.mattertypeid = ceh.mattertypeid left bring together calendar.eventtypecodes etc on etc.eventtypeid = se.eventtypeid order cc.casedisplaynumber alternative (maxdop 2) if object_id('tempdb..#chargedispo') not null drop table #chargedispo create table #chargedispo( casedisplaynumber varchar(20) , charge varchar(20) , chargeclass varchar(5) , dispositioncd varchar(2) ) insert #chargedispo select distinct cc.casedisplaynumber ,ccd.arscode charge ,ccc.chargeclass chargeclass ,dc.dispositioncd disposition courtcase cc bring together caseaction ca on ca.courtcaseid = cc.courtcaseid bring together partycaseactionrole pcar on pcar.caseactionid = ca.caseactionid bring together charge c on c.partycaseactionroleid = pcar.partycaseactionroleid bring together chargeactivity cay on cay.chargeid = c.chargeid , cay.chargestatusid = 1 bring together chargecodes ccd on ccd.chargecodeid = cay.chargecodeid bring together chargeclasscodes ccc on ccc.chargeclassid = ccd.chargeclassid left bring together chargedisposition cd on cd.chargeactivityid = cay.chargeactivityid left bring together dispositionstatuscodes dsc on dsc.dispositionstatusid = cd.dispositionstatusid left bring together dispositioncodes dc on dc.dispositionid = cd.dispositionid dsc.dispositionstatusid = 1 order ccc.chargeclass alternative (maxdop 2) select distinct cc.casedisplaynumber casenumber ,cc.filedate filedate ,en.fullname defendant ,cd.chargeclass class ,cd.dispositioncd dispositionid ,ne.eventtype eventtype ,ne.scheduledday scheduledday ,ne.eventresult eventresult ,pd.paydate duedate ,fngetfinancialbalance(fp.financialpartyid) balance courtcase cc bring together caseaction ca on ca.courtcaseid = cc.courtcaseid , cc.casestatusid = 1 bring together paydate pd on pd.caseactionid = ca.caseactionid , pd.enddate null bring together partycaseactionrole pcar on pcar.caseactionid = ca.caseactionid , pcar.partyroleid = 4 bring together financial.financialparty fp on fp.partyid = pcar.partyid bring together party p on p.partyid = pcar.partyid bring together partyrolecodes prc on prc.partyroleid = pcar.partyroleid , prc.partyroleid = 4 bring together entity e on e.entityid = p.entityid bring together entityname en on en.entityid = e.entityid bring together #chargedispo cd on cd.casedisplaynumber = cc.casedisplaynumber bring together #nextevent ne on ne.casedisplaynumber = cc.casedisplaynumber , ne.rownum = 1 ne.scheduledday <= pd.paydate , (cd.chargeclass = 'cv' or cd.chargeclass = 'pk') , (cd.dispositioncd = '11' or cd.dispositioncd = '12' or cd.dispositioncd = '21' or cd.dispositioncd = '22') , fngetfinancialbalance(fp.financialpartyid) > 0 order pd.paydate alternative (maxdop 2)

my problem in lastly statement. i'm not sure if needs there either. results perfect, need rid of rows if ne.mattertypeid = 3, , ne.scheduledday greater pd.paydate (basically rid of future courtroom dates after pay date). other that, want show else. help me out on future queries well. have been looking similar problems, can't find reply can help me out. unless i'm not searching right, perchance case. i've tried if, i've tried or's, i've tried case (which confuses me little). line:

ne.scheduledday <= pd.paydate

works, need little more specific.

the first thing need convert logic match way refer it. example, if "x needs greater y", code should reflect that, not opposite ("y needs <= x"). helps clarity.

your status

where not(ne.mattertypeid = 3 , ne.scheduledday > pd.paydate) , --...

second, issue date vs datetime problem. if dates datetime values, you'll need lop off time info casting date. may possible scheduled day 11/5/2014 8:00 pm, while pay date 11/5/2014 8:00 am. on surface, you'd expect them same, time value throwing off.

i recommend changing column type date if you'll never utilize time value. removes need lop off time in future queries. if can't that, can cast date create dates time-agnostic:

where not(ne.mattertypeid = 3 , cast( ne.scheduledday date) > cast(pd.paydate date) ) , --...

sql-server-2008 if-statement condition