同步批次和普通库存

begin tran
--
select sum(q) as stock_q,sum(q * tax_entry_price) as tax_entry_a,sum(q * a.retail_price) as tax_retail_a
from stk_fifo s,article a
where s.aid=a.aid

select sum(stock_q),sum(tax_entry_a) as tax_entry_a,sum(tax_retail_a) as tax_retail_a from article_storespace

 

update article_storespace
set article_storespace.stock_q=b.stock_q,
article_storespace.tax_entry_a=b.tax_entry_a,
article_storespace.tax_retail_a=b.tax_retail_a,
article_storespace.tax_avgentry_price=b.tax_avgentry_price
from
(
select store_id,s.aid,sum(q) as stock_q,sum(q * tax_entry_price) as tax_entry_a,sum(q * a.retail_price) as tax_retail_a,
sum(q * tax_entry_price)/sum(q) as tax_avgentry_price
from stk_fifo s,article a
where s.aid=a.aid
group by store_id,s.aid
having sum(q) <>0
)b
where article_storespace.storespace_id=b.store_id and article_storespace.aid=b.aid

select sum(q) as stock_q,sum(q * tax_entry_price) as tax_entry_a,sum(q * a.retail_price) as tax_retail_a
from stk_fifo s,article a
where s.aid=a.aid

select sum(stock_q),sum(tax_entry_a) as tax_entry_a,sum(tax_retail_a) as tax_retail_a from article_storespace

commit