quote:

---

Originally posted by longinos

Thanks Gail Shaw I really like your Code, but it did not do what I was trying to do, maybe because I didn't explain it well,
I have allready found a solution which is very amateur, but it works, I wish someone can do it with fewer lines, and a simpler way
Here it is:

create table qtyi(qtyiID int not null identity(1,1),prodID int, qty int,)
insert into qtyi(prodID, qty)
select distinct SOrderDetails.prodID, SUM(sorderdetails.qty) from SOrderDetails
inner join Product on SOrderDetails.prodID = Product.prodID group by SOrderDetails.prodID

create table qtyo(qtyoID int not null identity(1,1), prodID int, qty int)
insert into qtyo(prodID, qty)
select distinct COrderDetails.prodID, SUM(COrderDetails.qty) from COrderDetails
inner join Product on COrderDetails.prodID = Product.prodID group by COrderDetails.prodID

update Product set QI = qtyi.qty from qtyi inner join Product on qtyi.prodID = Product.prodID

update Product set QO = qtyo.qty from qtyo inner join Product on qtyo.prodID = Product.prodID

drop table qtyi
drop table qtyo

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by longinos

Thanks Gail Shaw I really like your Code, but it did not do what I was trying to do, maybe because I didn't explain it well,
I have allready found a solution which is very amateur, but it works, I wish someone can do it with fewer lines, and a simpler way
Here it is:

create table qtyi(qtyiID int not null identity(1,1),prodID int, qty int,)
insert into qtyi(prodID, qty)
select distinct SOrderDetails.prodID, SUM(sorderdetails.qty) from SOrderDetails
inner join Product on SOrderDetails.prodID = Product.prodID group by SOrderDetails.prodID

create table qtyo(qtyoID int not null identity(1,1), prodID int, qty int)
insert into qtyo(prodID, qty)
select distinct COrderDetails.prodID, SUM(COrderDetails.qty) from COrderDetails
inner join Product on COrderDetails.prodID = Product.prodID group by COrderDetails.prodID

update Product set QI = qtyi.qty from qtyi inner join Product on qtyi.prodID = Product.prodID

update Product set QO = qtyo.qty from qtyo inner join Product on qtyo.prodID = Product.prodID

drop table qtyi
drop table qtyo

---

WHy are you taking distinct while you're grouping by prodid. it already returns only one record per prodid so distinct is not required.
Also i cant understand how your solution differs from that provided by GilaMonster.Can you explain why the provided solution didnt work with some data example?

---

Update Products 
SET qtyIn = coalesce(SuppTotalQty,qtyIn), 
qtyOut = coalesce(CustTotalQty,qtyOut)
from products 
left outer join (select sum(qty) as SuppTotalQty, prodID from supplierOrderDetails group by prodID) SuppTot on products.prodID = SuppTot.ProdID
left outer join (select sum(qty) as CustTotalQty, prodID from customerOrderDetails group by prodID) CustTot on products.prodID = CustTot.ProdID