Hi I am trying to select people from one table based off dates in another table.
So I am selecting everyone from my recruit table that are eligible to ship, but then looking in tblACNRequest to see if they have a current request to ship, if they do i don't want o show them. The new twist I added in is that there request can be canceled dtCancelAcn and if this is canceled I want to show them even if the dtExpire date is greater than cancel date.
r.strLastName + ', ' + r.strFirstName + ', ' + r.strMiddlename as Fullname,
from tblRecruit as r Inner Join
tblMeps as m on m.intMepsId = r.intMepsId Where r.dtProcessing >= dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),4) and
r.dtProcessing < dateadd(d, datediff(d, 0, '10/7/2002 12:00:00 AM'),5) and
intRecruitId in (Select intRecruitId from tblACNRequest where dtExpire <= getdate()) or
intRecruitid in (Select intRecruitId from tblACNRequest where dtCancelAcn is not null)
Order by strLastName