Hi all,

This is my first post at this forum. So, Please help me in my problem.

I have written following query which uses lots of joins. can you please remove extra joins which are insise subquery as well as outer query. I want to get all users who are active and are wholesaler but they have met wholesaler criteria ( i.e) purchased more than min amount between today and date when they became wholesalers but the difference between these two date must be equal to or greater than wholesale criteria duration.

My query is:

-- select all users whose wholesaler bit is on
-- and they are active
-- and they have purchased more than min amount
-- between today and their wholesaler startdate
-- where difference between today and wholesale startdate > = duration.

DECLARE @duration int
DECLARE @minAmount money

-- get Wholesale criteria from wholesalecriteria table
select @duration = duration,
@minAmount = minAmount
from
WholesaleCriteria
where
isActive = 1

select * ,w.*
from users u inner join Wholesalers w
on u.userid = w.userid
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()) >= @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
where o.DateCreated
--between dateadd(day, -@Duration, getdate()) and getdate()
between w.wholesalestartdate and getdate()
and datediff(day,w.wholesalestartdate,getdate()) >= @duration
group by O.customerid
having sum(od.subtotal) > @duration

Also , will following condition from above query work or fail.

where o.DateCreated
--between dateadd(day, -@Duration, getdate()) and getdate()
between w.wholesalestartdate and getdate()
and datediff(day,w.wholesalestartdate,getdate()) >= @duration

I have added it to get all orders which were created between today and the date when user became wholesaler but difference between 2 dates is >= @duration ( e.g 7 days). If it is not ok then is commented code better. please give me better solution to it as current query is very slow.

Waiting for your response,

Regards,
Asif Hameed