select a.fnumber as 物料代码,
a.fname as 物料名称,
a.fmodel as 规格,
b.Fqty as 数量,
c.fendbal/(case when c.fendqty=0 then 1 else c.FendQty end) as 结存表单价,
p.price as 采购发票单价,
sp.price as 价格管理表单价,
f.fname as 仓库
from t_icitem a
inner join icinventory b on a.fitemid=b.fitemid --icinventory 即时库存表
Inner join t_stock f on b.fstockid=f.fitemid --t_stock 存货表
left join
(
Select b.* from T_ICitem a
Left Join ICBal b On a.FItemID=b.FitemiD --icbal结存表
inner Join
(
Select FItemID,Max(FYear*12+FPEriod) as FQty from ICBal
Where FENdQTy>0
Group by FItemID
)s On b.FYear*12+b.FPEriod=s.FQty and b.FItemID=s.FItemID
) c on a.fitemid=c.fitemid
left join
(
Select FItemID,Max(fcheckdate)as price from icpurchaseentry bti inner join icpurchase btou on bti.finterid=btou.finterid
Group by FItemID
)p on a.fitemid=p.fitemid
left join
(
Select FItemID,Max(fquotetime)as price from t_supplyentry --fquotetime生效日期
Group by FItemID,fquotetime
)sp on a.fitemid=sp.fitemid
where f.fitemid in (701,705,707,708,709,710,711,712,713,715,716,719,720,721,722,724,725,726,733,735,18197,29785,45796) and b.fqty>0
推荐阅读