Link to home
Start Free TrialLog in
Avatar of rgb192
rgb192Flag for United States of America

asked on

when productid count is null have product count = 0


combine these two queries

first query finds
productid count
and sets p.ourinwarehouse = p.inwarehouse - productid count

but if productid count is NULL (meaning there are no products to count)
then p.ourinwarehouse is set to NULL which is wrong


p.ourinwarehouse = p.inwarehouse - productid count
1                          = 7                    - 6                        correct
null                       = 22                   - null                     WRONG should be 22=22-0                


so what I want is when productid count is null have product count = 0



update p set
        p.ourinwarehouse = p.inwarehouse - P_Cnt.prod_Cnt
from products p
        left outer join
        (
                select p2.productid, count(*) prod_Cnt from orderitems i
                        inner join orders o on o.orderid = i.orderid
                        right outer join payments y on o.orderid = y.orderid
                        inner join products p2 on i.productid = p2.productid
                where o.statusid in (9,10,90,180,260)
                group by p2.productid
        )  P_Cnt on p.productid = P_Cnt.productid
where p.folderid = 119 and p.ourinwarehouse2 is null

update products p set p.ourinwarehouse = p.inwarehouse where p.ourinwarehouse is null and p.folderid=119
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rgb192

ASKER

thanks