SQL Query where conditions

Hi All,

I need assistance on the below query:

select    
 igive.igivepageid as IGIVEPAGE,    
 sum(o.totalamountinr)as AMOUNTRAISED,
 manufacturer.name as NGOName,  
 count(distinct(orders_shoppingcart.customerid)) as UNIQUEDONORS,
 igive.InstitutionType,
 manufacturer.manufacturerid as NgoId
 from igive with (nolock)  
 inner join orders_shoppingcart with (nolock) on igive.igivepageid=orders_shoppingcart.igivepageid    
  inner join manufacturer with (nolock) on manufacturer.manufacturerid=igive.manufacturerid    
   Inner Join Orders o with (nolock) ON o.OrderNumber = orders_shoppingcart.OrderNumber
   where  eventid = 41
    and orders_shoppingcart.createdon between @StartDate and @EndDate    
      and orders_shoppingcart.status not in ('Pending','Cancelled')
       and o.mop_id not in ('PM06')
        and (igive.InstitutionType IS NULL or igive.InstitutionType ='NGO')
         group by igive.igivepageid, manufacturer.name, igive.InstitutionType, manufacturer.manufacturerid

The above query is working fine, but I need to put more filter on where condition like
mop_id not in ('PM01') with only orders_shoppingcart.status in ('Pending').

could anyone help me out ?
thanks in advance
giveindiaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArgentiCommented:
replace
where  eventid = 41
    and orders_shoppingcart.createdon between @StartDate and @EndDate    
      and orders_shoppingcart.status not in ('Pending','Cancelled')
       and o.mop_id not in ('PM06')
        and (igive.InstitutionType IS NULL or igive.InstitutionType ='NGO')

Open in new window

by
where  eventid = 41
    and (orders_shoppingcart.createdon between @StartDate and @EndDate)
      and ((orders_shoppingcart.status not in ('Pending','Cancelled') and o.mop_id not in ('PM06')) 
         or (orders_shoppingcart.status in ('Pending') and o.mop_id not in ('PM01')))
      and (igive.InstitutionType IS NULL or igive.InstitutionType ='NGO')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
No pounts pl, juts a comment

using more extreme indenting may make this clearer:
WHERE eventid = 41
    AND ( orders_shoppingcart.createdon BETWEEN @StartDate AND @EndDate )
    AND (
            (
                orders_shoppingcart.STATUS NOT IN ('Pending', 'Cancelled')
                AND o.mop_id NOT IN ('PM06')
                )
            OR (
                orders_shoppingcart.STATUS IN ('Pending')
                AND o.mop_id NOT IN ('PM01')
                )
        )
    AND (
            igive.InstitutionType IS NULL
            OR igive.InstitutionType = 'NGO'
        )

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.