create function UnqualifiedWholesalers()
returns table 
as
return 

select u.userid
       ,u.isactive as isActiveUser
       ,w.isactive as isActivewholesaler
       ,u.iswholesaler
       ,w.IsValidationRequired
from users u 
inner join Wholesalers w
       on u.userid = w.userid
inner join WholesaleCriteria wc
       on wc.isActive = 1
where  w.isactive = 1 -- select active wholesaers only
and    u.isactive = 1 -- select active users only
and    u.iswholesaler = 1 -- select wholesalers only
and    w.IsValidationRequired = 1 -- temporary Wholesalers who must meet wholesalecriteria
and    datediff(day,w.wholesalestartdate,getdate()) >= wc.duration -- difference od wholesalestartdate and today >= @duration
and    u.userid in
       ( -- select all customers who have purchased more than minamount between their wholesalestartdate and today
       select O.Customerid
       from orders o 
       inner join orderdetails od
              on o.Orderid = od.Orderid
       inner join wholesalers w
              on w.userid = o.customerid
       inner join WholesaleCriteria wc
              on wc.isActive = 1
       where  o.DateCreated between w.wholesalestartdate and getdate()
       and    datediff(day,w.wholesalestartdate,getdate()) >= wc.duration
       and    w.ReminderSentOn >= DATEADD(DAY, 1 , GETDATE())
       group by O.customerid
       having sum(od.subtotal) < max(wc.minAmount) --min/max doesn't matter - only one value
       )

go

select * from UnqualifiedWholesalers() where isValidationRequired = 0